INDIRECT for range of sheets

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

Guest

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,
 
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)
 
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.
 
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.
 
Thanks, but I've written some VBA to generate the formula for me based on
first and last sheets.

Cheers,

Tom.
 
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").
 
Back
Top