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.

"Pete_UK" wrote:

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


On Nov 12, 11:51 pm, jhgravelle
wrote:
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.



"T. Valko" wrote:
Why are you trying to use INDIRECT?

--
Biff
Microsoft Excel MVP

"jhgravelle" wrote in message
...
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?- Hide quoted text -

- Show quoted text -