SUM Single Cell in Multiple Cell Referenced Worksheets

[KnightFhawker]

Hi everyone,

This is my first post on here so I hope I follow the procedure appropriately. Please feel free to offer any feedback if necessary.

Despite hours of searches and reference material I have looked though, I cannot figure out why the following formula ends up in a #REF!. It seems to follow almost all related formats that I have found, yet I have not necessarily found this exact situation to confirm the issue. Any suggestions would be much appreciated.

I am simply trying to SUM all $M29 cells across all of the existing worksheets within the range provided. I want to ensure the worksheet range is updated based on the cell references found in B94 (containing the First Worksheet name within the range) and D94 (containing the Last Worksheet name within the range).

Each of the cells contain the following formulas with the respective result:

B94: =B98 First Worksheet Name
D94: =LOOKUP(2,1/(B:B<>""),B:B) Last Worksheet Name

F48: =SUM(INDIRECT("'"&$B$94&":"&$D$94&"'!$M29")) #REF!

Thank you in advance for your help.

 

[Weazel]

unfortunately the indirect doesn't support 3d cell references.

an alternative might be adding 2 sheets to your workbook say 'start' and 'end' then =sum(start:end!M29) would add all the values in M29 between the 2 sheets.

 

[Aladin Akyurek]

@KnightFhawker

You appear to have the names of the relevant sheets in column B and want to say sum M29 of the sheets FROM nth sheet TO the last sheet.

If you would be able to specify the FROM sheet, you can have:

=SUMPRODUCT(SUBTOTAL(9,INDIRECT("'"&INDEX(B:B,98):INDEX(B:B,MATCH(REPT("z",255),B:B))&"'!M29")))

The bolded part should be a number or a MATCH expression like:

MATCH(sheet name to start with,B:B,0)

 

[KnightFhawker]

I guess the fact that INDIRECT doesn't allow 3D expressions explains my issue. Thank you for the explanation.

I believe the the First and Last worksheet might be the best way to proceed for me at this point, but I will see about incorporating the SUMPRODUCT option if I can make it work. Thank you both for your help and taking the time to help me with this issue. I greatly appreciate it.

 

[FDibbins]

Another option might be to use something like this...
=SUMPRODUCT(SUMIF(INDIRECT("'"&Tabs&"'!T6:T1000"),C794,INDIRECT("'"&Tabs&"'!M6:M1000")))

You would need to create a list of sheet names, and give it the range name Tabs
Obviously, adjust ranges as needed

 

[KnightFhawker]

Thank you FDibbins!

I was able to take your suggestion and meld it together with a formula within the Name Manager to allow the dynamic adjustment to flow through with the INDIRECT function.

[Cell: =Formula Result]

What I did was create 2 cells, one grabs the first worksheet name in the list and the second grabs the last:

B94: =B98 First Worksheet Name

D94: =LOOKUP(2,1/(B:B<>""),B:B) Last Worksheet Name


The next two cell grab the cell address for each of the above results:


B95: {=CELL("address",INDEX($B$98:$B$250,MATCH($B$94,$B$98:$B$250,0),1))} First Worksheet Name Cell Address

D95: {=CELL("address",INDEX($B$98:$B$250,MATCH($D$94,$B$98:$B$250,0),1))} Last Worksheet Name Cell Address


Next, I set an extended range of cells (B98-B250) that includes both current and empty cells that could in the future hold worksheet names as they are added, calling this range TABS as FDibbins suggested.

Then, within the Name Manager, I associated this formula with the TABS range name in order to grab the cell values in B95 and D95:

Name Manager, TABS: =INDIRECT("'SUMMARY'!"&SUMMARY!$B$95&":"&SUMMARY!$D$95) 'SUMMARY'!(First Worksheet Name Cell Address):(Last Worksheet Name Cell Address)


Finally, I used the following formula to combine the TABS range with the actual cell I want to sum across the range of cell within TABS:

F48 =SUMPRODUCT(SUMIF(INDIRECT("'"&TABS&"'!$M29"),"<>0")) Displays the Sum of each M29 cell across all worksheets within the TABS range


In case anyone is interested I used the following VBA associated with a button labeled REFRESH in order to grab and list all of the worksheet names within B98:B250:
(keep in mind that I set this up when I was using the F(first) and L(last) tabs to enclose the range as suggested by Weazel)
(Also, the IF section is there strictly to skip listing worksheets that are not intended to be listed, and can be removed if you are not needing to skip any of the worksheets)
(The i is set to start at 2 in order to skip the first "Summary" worksheet, so if you would like to start it at the first worksheet then "i" would need to be 1 instead)
(Finally, the +94 after the "i" within the Cells line is simply there to shift the row of where the results will be listed, with the 2 representing column B. Adjustments were made to the 94 due to skipped worksheets. I'm sure there's a more efficient way but didn't take the time to work on it)

Sub SheetNames()

Dim i As Integer


Sheets("Summary").Range("B98:B250").ClearContents

For i = 2 To Sheets.Count

If (Sheets(i).Name = "F" Or Sheets(i).Name = "L" Or Sheets(i).Name = "Input") Then GoTo 1

Cells(i + 94, 2) = Sheets(i).Name
1
Next i
End Sub



Thank you again to all of you for your suggestions! I truly appreciate the help.