3

I can easily sum a given cell, say B9, across multiple worksheets with something like:

=SUM(Sheet7:Sheet11!B9)

This is the equivalent to :

=Sheet7!B9+Sheet8!B9+Sheet9!B9+Sheet10!B9+Sheet11!B9

Which is the sum of cell B9 in all the worksheets from Sheet7 through Sheet11.

I need to create something like the first formula, but with the sheet names in cells. So if A1 contains:

Sheet7

and cell A2 contains:

Sheet11

enter image description here

Then the formula in A3 would use these cells. What I have tried so far is:

=SUM(A1 & ":" & A2 & "!" & B9)

and

=SUM(INDIRECT(A1 & ":" & A2 & "!" & B9))

Neither of these work.

I can easily create a VBA function to do this, but the workbook must be able to operate in a macro-free environment.

  • try this =SUM(INDIRECT(A1 & ":" & A2 & "!" & "B9")) – KingOfAllTrades May 7 '15 at 20:55
  • @KingOfAllTrades Sadly, I get #REF! – Gary's Student May 7 '15 at 22:00
  • I don't think INDIRECT works well with multiple sheets. I can get all kinds of ranges to work with INDIRECT on a single sheet. – KingOfAllTrades May 8 '15 at 1:23
1

Based on @Dave’s answer and your comment But I don't know in advance what the contents of cells A1 and A2 will be.......so I don't know how many terms to include. I would suggest to add a column to include the total of each sheet and sum that column. Say you enter the sheet names in column A, then enter in column B:

=IF(ISNUMBER(INDIRECT(A2&"!B9")),INDIRECT(A2&"!B9"),0) 

Than you can do =Sum(B:B) to get the total.

Note that I added the IF and ISNUMBER function to avoid the #REF! when column A is missing a sheet name. You may want to use ISBLANK instead, as

=IF(ISBLANK(A2),0,INDIRECT(A2&"!B9"))

If you do want to get an error message when column A has a wrong sheet name.

Another tip: If you name your sheets as Sheet1, Sheet2, you can easily drag to fill your sheets in column A

| improve this answer | |
1

This is a manual approach which may not be ideal

=INDIRECT(A1&"!A1")+INDIRECT(A2&"!A1")

or

=SUM(INDIRECT(A1&"!A1"),INDIRECT(A2&"!A1"))

My worksheet looked like

     A         B         C
1    sheet2
2    sheet3
3

And in Sheet2 A1 had a value as does Sheet3 A1

In your case, you would need

   =SUM(INDIRECT(A1&"!A1"),INDIRECT(A2&"!A1"),INDIRECT(A3&"!A1"),INDIRECT(A4&"!A1")...etc)
| improve this answer | |
  • Thank you for this response. I tried the second formula and it correctly summed the values for Sheet7 and Sheet11, but none of the sheets in between. – Gary's Student May 7 '15 at 14:11
  • no sadly, you will need to enter each sheet as a new cell value in your worksheet1... – Dave May 7 '15 at 14:29
  • But I don't know in advance what the contents of cells A1 and A2 will be.......so I don't know how many terms to include. – Gary's Student May 7 '15 at 14:31
  • You may want to update your question with that :( – Dave May 7 '15 at 14:41
  • I have added the sentence which, hopefully clarifies my request......... ** once again thank you for your help. ** – Gary's Student May 7 '15 at 14:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.