Indirect a range that spans multiple sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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
 
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.
 
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
 
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
 
Back
Top