Sum across worksheets with indirect
 

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.

Sum across worksheets with indirect
 

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

"Tom Hayakawa" wrote:

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.

Sum across worksheets with indirect
 

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

=SUM(Start:End!D16)


"Tom Hayakawa" wrote:

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.

Sum across worksheets with indirect
 

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.

"Teethless mama" wrote:

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

=SUM(Start:End!D16)


"Tom Hayakawa" wrote:

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.

Sum across worksheets with indirect
 

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

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

HTH

"Tom Hayakawa" wrote:

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.

"Teethless mama" wrote:

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

=SUM(Start:End!D16)


"Tom Hayakawa" wrote:

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.

Sum across worksheets with indirect
 

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.

"Toppers" wrote:

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

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

HTH

"Tom Hayakawa" wrote:

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.

"Teethless mama" wrote:

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

=SUM(Start:End!D16)


"Tom Hayakawa" wrote:

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.