Indirect a range that spans multiple sheets
 

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?

Indirect a range that spans multiple sheets
 

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?

Indirect a range that spans multiple sheets
 

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?

Indirect a range that spans multiple sheets
 

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 -

Indirect a range that spans multiple sheets
 

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 -

Indirect a range that spans multiple sheets
 

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

On Nov 13, 2:36 pm, jhgravelle
wrote:
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 -- Hide quoted text -

- Show quoted text -

Indirect a range that spans multiple sheets
 

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




"jhgravelle" wrote in message
...
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 -