Sum across worksheets with indirect

[Guest]

Hello All,

I'm trying to get the sum of values in the same cell across a range of
several worksheets. The range of the worksheets can change over time, as
well. For instance, the formula SUM('1:2'!D16) might also need to be
SUM('21:26'!D16). The worksheets will always be in a contiguous range. I'm
trying to use the INDIRECT function to get the worksheet numbers into the SUM
formula, but I'm not having much luck. For instance, this is what I tried,
but it doesn't work:

=SUM(INDIRECT(CONCATENATE("'",E3,":",F3,"'!D16")))

where E3 is the first worksheet and F3 is the last worksheet in the range.
Any ideas on how to do this so it works? Thanks in advance.

 

[Guest]

Try:

Add worksheet "First" before first in your w/sheet range and "Last" at end
of range.

In worksheet with formula (outside w/list I assume):

=SUM(First:Last!D16)

HTH

 

[Guest]

Create Start and End sheets, Start sheet on the far left tab, and End sheet
on the far right tab.

=SUM(Start:End!D16)

 

[Guest]

Hi Teethless and Toppers,

Thanks for the help, but if my range changes, won't using a first and last
worksheet just collect the entire range? I wasn't as clear as I should have
been. I have several worksheets, 1 through 31 ( the days of the month), and
I need to collect data from each day in a given range. Sometime it might be
from days 1 and 2 only. Other times it may be days 21 through 26, or 10
through 15. The ranges will always be contiguous, ie, never 1 through 4 and
6 through 11. I want to exclude any values from any of the other worksheets
outside the selected range. Thanks for your quick response.

 

[Guest]

Just move the Start/End sheets to fit your range.

Start,1,2,End for Days 1 & 2

HTH

 

[Guest]

Toppers,

Thanks, but that's not going to help, since the ranges will change, and I
don't want to put the person using the spreadsheet in the position of having
to move things around to get an answer. I did find a formula T. "Biff" Valko
suggested in another post:

SUMPRODUCT(SUMIF(INDIRECT("'"&E3:F3&"'!D16"),"<>"))

That seems to work, but I have no idea why - if I use direct values instead
of the INDIRECT function it fails.

My thanks to both you and Teethless Mama for your time and attention.