Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HLookup indirect multiple worksheets | Excel Worksheet Functions | |||
Use of Indirect on two worksheets | Excel Worksheet Functions | |||
how to use 2 worksheets in one formula with INDIRECT & VLOOKUP | Excel Worksheet Functions | |||
INDIRECT.EXT problem with missing worksheets | Excel Worksheet Functions | |||
INDIRECT(ADDRESS... Across worksheets | Excel Worksheet Functions |