using indirect for a 3d sum range

jaymri

New Member
Joined
Jul 20, 2007
Messages
3
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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Derek Brown

Well-known Member
Joined
Dec 26, 2005
Messages
2,390
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:
Code:
=SUM(Sheet2:Sheet5!B10)

To sum a range of cells on a range of worksheets:
Code:
=SUM(Sheet2:Sheet5!A1:B10)

To indirectly sum cells on a range of worksheets:
Code:
=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:
Code:
Sheet4!A1
to indirectly SUM the content of, in this example, Cell A1
 

jaymri

New Member
Joined
Jul 20, 2007
Messages
3
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,646
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 20, 2007
Messages
3
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

Well-known Member
Joined
Aug 29, 2004
Messages
819
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!
 

Forum statistics

Threads
1,181,647
Messages
5,931,210
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top