using indirect for a 3d sum range

[jaymri]

I am trying to use the indirect function to sum over a 3d range.

I would like to use indirect instead of :
=sum(Sheet2!:Sheet5!:b10)

however even just typing in the text does not work
=sum(indirect("Sheet2!:Sheet5!:b10"))
putting Sheet names within ''s also doesn't seem to work.
=sum(indirect("'Sheet2'!:'Sheet5'!:b10"))



a1="Sheet2"
a2="Sheet5"
=sum(indirect(a1$&"!:"&a2&"!:b10"))

If you have a solution that would work on Sheet2!A7:Sheet5!:b10 that would be great.

I believe that I have tried dozens of variations of double and single quotes. If anyone can help, thanks,

Thanks in advance.

 

[Derek Brown]

Note quite sure that I understand, from your examples, what you are trying to achieve but here are some examples that may help.

To sum the same cell on the range of worksheets:

=SUM(Sheet2:Sheet5!B10)

To sum a range of cells on a range of worksheets:

=SUM(Sheet2:Sheet5!A1:B10)

To indirectly sum cells on a range of worksheets:

=SUM(INDIRECT(Sheet2!B10),INDIRECT(Sheet3!B10),INDIRECT(Sheet4!B10),INDIRECT(Sheet5!B10))

but note that, for the INDIRECT to work then cell B10 must include a reference to the sheet as well as the cell. eg Cell B10 on Sheet4 would contain:

Sheet4!A1

to indirectly SUM the content of, in this example, Cell A1

 

[jaymri]

thanks, but comma separated 2d sum ranges is not what I need to do. I understand your solution.

I need to sum over a flexible 3d range

=sum(Sheet2!:Sheet5!:b10) and/or =sum(Sheet2!A5:Sheet5!:b10)

assume my numbers are in cells B10 through B20. and are on sheet FirstSheet through sheet LastSheet.

taking it in steps

=sum(FirstSheet:LastSheet!B12) works

=sum(indirect("LastSheet!B12)) works

=sum(indirect("'FirstSheet'!B10:B20)) works

=sum(indirect("'FirstSheet'!B10:'LastSheet'!B20")) #REF

I have tried many combinations of " and '


I put the text in the example above instead of what I will really use when it works, something like
=sum(indirect(A5))
A5="'FirstSheet'!B10:'LastSheet'!B20" or

A5= "'"&A6&"'!B10:'&A7&"'!B20" where
A6="FirstSheet" and A7="LastSheet"

I hope that I have made a clearer example.

Again, Thanks in advance.

 

[Domenic]

First, list your sheet names in a range of cells, let's say A2:A5. Then, let B2 contain the name of the first sheet, and C2 the name of the last sheet. Then try the following formulas...

=SUMPRODUCT(--(N(INDIRECT("'"&INDEX($A$2:$A$5,MATCH(B2,$A$2:$A$5,0)):INDEX($A$2:$A$5,MATCH(C2,$A$2:$A$5,0))&"'!B10"))))

=SUMPRODUCT(SUMIF(INDIRECT("'"&INDEX($A$2:$A$5,MATCH(B2,$A$2:$A$5,0)):INDEX($A$2:$A$5,MATCH(C2,$A$2:$A$5,0))&"'!A5:B10"),"<>"))

Note, however, depending on how your sheets are named, it may be possible to dispense with having to list them in a range of cells.

Hope this helps!

 

[jaymri]

thanks, but I really do not think it will work for me. but I'll try.

is INDIRECT unable to pass on a 3d range?

I really would need something very simple like

=sum(indirect("'FirstSheet'!B10:'LastSheet'!B20"))


=sum(indirect(A5))
A5="'FirstSheet'!B10:'LastSheet'!B20" or

A5= "'"&A6&"'!B10:'&A7&"'!B20" where
A6="FirstSheet" and A7="LastSheet"


thanks in advance.

 

[vane0326]

Put your first worksheet (2) in cell A1 and your last worksheet (5) in cell A2.


Then Try...

Go to the menu bar:

Insert > Name > Define

Name: Array

Refers to:

=EVALUATE(EVALUATE("if("&MID(GET.CELL(6,!A3),6,2 55)))

Click Ok

Then input formula in cell A3

=IF(1,Evaluate,"sum('"&A1&":"&A2&"'!B10)")

Hope it helps!