INDIRECT for range of sheets
 

I want to sum cell D4 across a range of sheets.

I've got the first and last sheet names:
Hello my name is Gilbert (in cell C1)
I like squirrels (in cell C2

I've therefore put together:
=SUM(INDIRECT("'"&C1&":"&C2&"'!D4"))

The problem is the INDIRECT returns #REF! - I think because it's not a
single sheet reference.

What am I doing wrong?
How else can I do this?

Cheers,

INDIRECT for range of sheets
 

Create two helper sheets Call "First" and "Last"
"First" is far left of the sheets
"Last" is far right of the sheets

=SUM(First:Last!D4)


"mr tom" wrote:

I want to sum cell D4 across a range of sheets.

I've got the first and last sheet names:
Hello my name is Gilbert (in cell C1)
I like squirrels (in cell C2

I've therefore put together:
=SUM(INDIRECT("'"&C1&":"&C2&"'!D4"))

The problem is the INDIRECT returns #REF! - I think because it's not a
single sheet reference.

What am I doing wrong?
How else can I do this?

Cheers,

INDIRECT for range of sheets
 

Thanks - but this is designed for presentation purposes, so I can't have the
extra sheets hanging around in there.

I'm playing with the idea of using some VBA to do the job - but had hoped
there was a way of getting it to work with formulas.

Cheers,

Tom.

"Teethless mama" wrote:

Create two helper sheets Call "First" and "Last"
"First" is far left of the sheets
"Last" is far right of the sheets

=SUM(First:Last!D4)


"mr tom" wrote:

I want to sum cell D4 across a range of sheets.

I've got the first and last sheet names:
Hello my name is Gilbert (in cell C1)
I like squirrels (in cell C2

I've therefore put together:
=SUM(INDIRECT("'"&C1&":"&C2&"'!D4"))

The problem is the INDIRECT returns #REF! - I think because it's not a
single sheet reference.

What am I doing wrong?
How else can I do this?

Cheers,

INDIRECT for range of sheets
 

You can *hide* those sheets!

Works just as well, whether they're visible or not.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
Thanks - but this is designed for presentation purposes, so I can't have the
extra sheets hanging around in there.

I'm playing with the idea of using some VBA to do the job - but had hoped
there was a way of getting it to work with formulas.

Cheers,

Tom.

"Teethless mama" wrote:

Create two helper sheets Call "First" and "Last"
"First" is far left of the sheets
"Last" is far right of the sheets

=SUM(First:Last!D4)


"mr tom" wrote:

I want to sum cell D4 across a range of sheets.

I've got the first and last sheet names:
Hello my name is Gilbert (in cell C1)
I like squirrels (in cell C2

I've therefore put together:
=SUM(INDIRECT("'"&C1&":"&C2&"'!D4"))

The problem is the INDIRECT returns #REF! - I think because it's not a
single sheet reference.

What am I doing wrong?
How else can I do this?

Cheers,

INDIRECT for range of sheets
 

Thanks, but I've written some VBA to generate the formula for me based on
first and last sheets.

Cheers,

Tom.

"RagDyeR" wrote:

You can *hide* those sheets!

Works just as well, whether they're visible or not.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
Thanks - but this is designed for presentation purposes, so I can't have the
extra sheets hanging around in there.

I'm playing with the idea of using some VBA to do the job - but had hoped
there was a way of getting it to work with formulas.

Cheers,

Tom.

"Teethless mama" wrote:

Create two helper sheets Call "First" and "Last"
"First" is far left of the sheets
"Last" is far right of the sheets

=SUM(First:Last!D4)


"mr tom" wrote:

I want to sum cell D4 across a range of sheets.

I've got the first and last sheet names:
Hello my name is Gilbert (in cell C1)
I like squirrels (in cell C2

I've therefore put together:
=SUM(INDIRECT("'"&C1&":"&C2&"'!D4"))

The problem is the INDIRECT returns #REF! - I think because it's not a
single sheet reference.

What am I doing wrong?
How else can I do this?

Cheers,

INDIRECT for range of sheets
 

One way is to use a defined name. Enter

=IF(1,Evaluate,"sum('"&C1&":"&C2&"'!D4)")

in a cell. Then select A1 and define "Evaluate" to refer to:

=EVALUATE(EVALUATE("if("&MID(GET.CELL(6,!A1),6,255 )))

This should work for any formula of the form =IF(1,Evaluate,"String").





On 23 Apr, 13:32, mr tom <mr-tom at mr-tom.co.uk.(donotspam) wrote:
I want to sum cell D4 across a range of sheets.

I've got the first and last sheet names:
Hello my name is Gilbert (in cell C1)
I like squirrels (in cell C2

I've therefore put together:
=SUM(INDIRECT("'"&C1&":"&C2&"'!D4"))

The problem is the INDIRECT returns #REF! - I think because it's not a
single sheet reference.

What am I doing wrong?
How else can I do this?

Cheers,

INDIRECT for range of sheets
 

Try this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&C1:C2&"'!D4"),"<") )

Biff

"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
I want to sum cell D4 across a range of sheets.

I've got the first and last sheet names:
Hello my name is Gilbert (in cell C1)
I like squirrels (in cell C2

I've therefore put together:
=SUM(INDIRECT("'"&C1&":"&C2&"'!D4"))

The problem is the INDIRECT returns #REF! - I think because it's not a
single sheet reference.

What am I doing wrong?
How else can I do this?

Cheers,