Use of Indirect Function

[Chris Gorham]

Hi,

I'm trying to sum the same cell across a number of
worksheets using the following formula

=SUM(aaa:bbb!C1)
where aaa is the name of the first sheet, bbb is the name
of the last sheet and C1 is the cell being summed...
this works - but what I want to do is replace bbb with a
variable using the INDIRECT function. This means that I
can add sheets onto the end of the workbook and by
updating a single cell all the SUM formulas will now add
through to the final sheet.

Can't get the syntax to work....any help appreciated

Rgds....Chris

 

[JE McGimpsey]

I could be wrong, but I don't think INDIRECT returns three-D references.

however, I sometimes do this by inserting a blank sheet named "First"
before the first sheet to sum and a blank sheet named "Last" after the
last sheet. Then, using =SUM(First:Last!C1) allows adding any number of
sheets before after First and before Last without changing the formula,
or having to use an additional cell.

 

[firefytr]

Hi,

Maybe ...

=SUM(aaa:INDIRECT("'"&A1&"'!"&C1)

 

[hgrove]

JE McGimpsey wrote...

I could be wrong, but I don't think INDIRECT returns three-D
references.

You're right. INDIRECT only returns references to *Range* objects, an
3D references aren't range objects, so INDIRECT can't return 'em.

however, I sometimes do this by inserting a blank sheet named
"First" before the first sheet to sum and a blank sheet named
"Last" after the last sheet. Then, using =SUM(First:Last!C1)
allows adding any number of sheets before after First and
before Last without changing the formula, or having to use an
additional cell.

But if the OP wants to dynamically specify the worksheets over which t
sum, this won't work.

The only relatively robust way to do this is to create an ordered lis
of worksheet names like

aaa
bbb
ccc
ddd
eee
fff

give the list a name like WSList, then use an abomination like

=SUMPRODUCT(SUMIF(INDIRECT("'"&OFFSET(WSList,
MATCH("bbb",WSList,0)-1,0,MATCH("eee",WSList,0)
-MATCH("bbb",WSList,0),1)&"'!X99"),"<>0"))

You can guage the true level of Microsoft's passion to help Excel user
reach their potential by how many *more* years it'll take them t
implement a 3D analog to INDIRECT

 

[firefytr]

Well ya learn something new everyday! I seem to remember trying tha
out a couple of months ago and failing miserably. My apologies for no
thinking before posting that. :