Sum a dynamic range across multiple sheets (make 3d reference dynamic)

[Super_mario]

I have a workbook with multiple sheets.
The sheets are named "First", Jan, Feb, Mar......., "Last" in that order.
I'd like to add the same cell on the different months tab from the First to a certain month date (using a drop-down list).
I thought =SUM(INDIRECT("FIRST:"&TEXT(A1,0)&"!A2")) where A1 is the dropdown list with the months and A2 the cell to sum.
Unfortunately it gives me #REF! as the 3D reference don't support INDIRECT

Thanks in advance for any suggestion

 

[XLearner]

Hi,

This doesn't work either if the INDIRECT you built is in a Name variable. Possible workaound...

1. In a cheat sheet that you can hide later list all your sheet names including and starting with First
2. Select the range > Define name i.e. SheetNames
3. In sheet First, in the cell where you want your total:

=SUMPRODUCT(SUM(INDIRECT(INDEX([B][I][COLOR=#0000cd]SheetNames[/COLOR][/I][/B],1):INDEX([B][I][COLOR=#0000cd]SheetNames[/COLOR][/I][/B],MATCH($A$1,[B][I][COLOR=#0000cd]SheetNames[/COLOR][/I][/B],0)) & "!$A$2")))

To ensure this will work even if the name of any sheet change you can build the current list with formula:

=MID(CELL("filename",[B]First[/B]!$A$2),FIND("]",CELL("filename",[B]First[/B]!$A$2))+1,LEN(CELL("filename",[B]First[/B]!$A$2)))

copy it down as necessary then edit (starting the 2nd) each formula to replace First with Jan, Feb...Last

Drop down list in First!A1 should refer to =SheetNames to reflect any change

See SUM_Dynamic3DRange.xlsx if necessary

Regards
XLeaner

 

[Aladin Akyurek]

I have a workbook with multiple sheets.
The sheets are named "First", Jan, Feb, Mar......., "Last" in that order.
I'd like to add the same cell on the different months tab from the First to a certain month date (using a drop-down list).
I thought =SUM(INDIRECT("FIRST:"&TEXT(A1,0)&"!A2")) where A1 is the dropdown list with the months and A2 the cell to sum.
Unfortunately it gives me #REF! as the 3D reference don't support INDIRECT

Thanks in advance for any suggestion

Try to number the sheets you have as 1, 2, 3,...

In A1 of your main sheet, enter a desired month number, say, 5.

In A2 enter:

=SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT("1:"&$A$1))&"'!A2")))