Indirect a range that spans multiple sheets

J

jhgravelle

I'm having a problem with indirect.

I'd like to get
=SUM('Section A:Section B'!A1)

As I step through my formula I get to here, and then get #Ref!
=SUM(INDIRECT("'Section A:Section B'!A1"))

Is there any way to do this?
 
J

jhgravelle

because the full formula will use concatenate or &'s no construct 'Section
A:Section B'!A1. I know that part is working, either buy using the formula
auditor, or editing and selecting portions of the formula and pressing F9.
what's not working is the inderect portion.
 
P

Pete_UK

Well, if you are building up a string of "'Section A:Section B'!A1",
you may as well go the whole hog and make it:

"SUM('Section A:Section B'!A1)"

Let's say that this is in D5. Then you can make use of this user-
defined function:

Function Eval(Rng As Range) As Variant
Application.Volatile True
Eval = Evaluate(Rng.Text)
End Function

by means of this formula:

=Eval(D5)

in cell E5 (say).

Hope this helps.

Pete
 
J

jhgravelle

Thanks for the other ways to do it. I wouldn't have come up with such short
vba code, but i could do it through various ways in VBA. I was looking for
using simple excel formula like one would think they could be used. But it
appears that INDIRECT cant always convert a string to a reference.
 
P

Pete_UK

I don't think INDIRECT works very well with 3-d references. The Eval
UDF is very useful for converting strings into formulae - certainly
worth adding to your library of routines.

Pete
 
P

Peo Sjoblom

INDIRECT is limited with regards of converting a text string spanning more
than one sheet, it needs all the sheet names not just the first and last,
one possible way would be to use

=SUM(N(INDIRECT("'Section "&{"A";"B"}&"'!A1")))


and if the Section is part of all sheet names you can put the other part of
the names within those
curly brackets


--


Regards,


Peo Sjoblom
 
Top