Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Summing across multiple sheets with a twist
Every week a new worksheet ("tab") is made by copying the prior week's tab and adding pertinent data for the week. Several of the cells in each "tab" are a summation across all prior worksheets/tabs, giving Year-To-Date totals. I figured out how to do this by clicking the leftmost "tab" and then shift-clicking the rightmost tab. My issue: I'd like for this to automatically happen when I create a new tab, but I get #REF's for all these references as soon as I make the copy. I've got an idea for a workaround, but I can't get it to work: 1) Put a cell in each "tab" with the name of the "tab"... e.g. cell C3 has "525" in it. 2) In the year-to-date summation cells which normally would have a formula like: =SUM('501:525'!X7) I've tried putting =SUM('501:C3'!X7). Didn't work. I've tried putting =SUM('501:"C3"'!X7) thinking maybe the quotes would let Excel know I'm trying to insert a "text string" where the sheetname/tab would normally be. Didn't work. Basically, I'm asking if there's a way to force a sheetname into a formula by making a reference to a cell with that sheetname in it? Or does anyone know a better workaround for what I'm trying to accomplish? Right now, I just manually go in and redo all the #REF's... no big deal but my curiosity has got the better of me. Thanks -- hillmic ------------------------------------------------------------------------ hillmic's Profile: http://www.excelforum.com/member.php...o&userid=24651 View this thread: http://www.excelforum.com/showthread...hreadid=382350 |
#2
|
|||
|
|||
Try
=SUM(INDIRECT("'501:"&C3&"'!X7")) although this would suggest that all you need is the last sheet name in the summary tab, not on each. -- HTH RP (remove nothere from the email address if mailing direct) "hillmic" wrote in message ... Every week a new worksheet ("tab") is made by copying the prior week's tab and adding pertinent data for the week. Several of the cells in each "tab" are a summation across all prior worksheets/tabs, giving Year-To-Date totals. I figured out how to do this by clicking the leftmost "tab" and then shift-clicking the rightmost tab. My issue: I'd like for this to automatically happen when I create a new tab, but I get #REF's for all these references as soon as I make the copy. I've got an idea for a workaround, but I can't get it to work: 1) Put a cell in each "tab" with the name of the "tab"... e.g. cell C3 has "525" in it. 2) In the year-to-date summation cells which normally would have a formula like: =SUM('501:525'!X7) I've tried putting =SUM('501:C3'!X7). Didn't work. I've tried putting =SUM('501:"C3"'!X7) thinking maybe the quotes would let Excel know I'm trying to insert a "text string" where the sheetname/tab would normally be. Didn't work. Basically, I'm asking if there's a way to force a sheetname into a formula by making a reference to a cell with that sheetname in it? Or does anyone know a better workaround for what I'm trying to accomplish? Right now, I just manually go in and redo all the #REF's... no big deal but my curiosity has got the better of me. Thanks -- hillmic ------------------------------------------------------------------------ hillmic's Profile: http://www.excelforum.com/member.php...o&userid=24651 View this thread: http://www.excelforum.com/showthread...hreadid=382350 |
#3
|
|||
|
|||
Try...
=SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT("501:"&C3) )&"'!X7"))) Hope this helps! In article , hillmic wrote: Every week a new worksheet ("tab") is made by copying the prior week's tab and adding pertinent data for the week. Several of the cells in each "tab" are a summation across all prior worksheets/tabs, giving Year-To-Date totals. I figured out how to do this by clicking the leftmost "tab" and then shift-clicking the rightmost tab. My issue: I'd like for this to automatically happen when I create a new tab, but I get #REF's for all these references as soon as I make the copy. I've got an idea for a workaround, but I can't get it to work: 1) Put a cell in each "tab" with the name of the "tab"... e.g. cell C3 has "525" in it. 2) In the year-to-date summation cells which normally would have a formula like: =SUM('501:525'!X7) I've tried putting =SUM('501:C3'!X7). Didn't work. I've tried putting =SUM('501:"C3"'!X7) thinking maybe the quotes would let Excel know I'm trying to insert a "text string" where the sheetname/tab would normally be. Didn't work. Basically, I'm asking if there's a way to force a sheetname into a formula by making a reference to a cell with that sheetname in it? Or does anyone know a better workaround for what I'm trying to accomplish? Right now, I just manually go in and redo all the #REF's... no big deal but my curiosity has got the better of me. Thanks |
#4
|
|||
|
|||
oops, seeing Domenic's answer reminds me that this method doesn't work
across sheets, sorry about that. -- HTH RP (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... Try =SUM(INDIRECT("'501:"&C3&"'!X7")) although this would suggest that all you need is the last sheet name in the summary tab, not on each. -- HTH RP (remove nothere from the email address if mailing direct) "hillmic" wrote in message ... Every week a new worksheet ("tab") is made by copying the prior week's tab and adding pertinent data for the week. Several of the cells in each "tab" are a summation across all prior worksheets/tabs, giving Year-To-Date totals. I figured out how to do this by clicking the leftmost "tab" and then shift-clicking the rightmost tab. My issue: I'd like for this to automatically happen when I create a new tab, but I get #REF's for all these references as soon as I make the copy. I've got an idea for a workaround, but I can't get it to work: 1) Put a cell in each "tab" with the name of the "tab"... e.g. cell C3 has "525" in it. 2) In the year-to-date summation cells which normally would have a formula like: =SUM('501:525'!X7) I've tried putting =SUM('501:C3'!X7). Didn't work. I've tried putting =SUM('501:"C3"'!X7) thinking maybe the quotes would let Excel know I'm trying to insert a "text string" where the sheetname/tab would normally be. Didn't work. Basically, I'm asking if there's a way to force a sheetname into a formula by making a reference to a cell with that sheetname in it? Or does anyone know a better workaround for what I'm trying to accomplish? Right now, I just manually go in and redo all the #REF's... no big deal but my curiosity has got the better of me. Thanks -- hillmic ------------------------------------------------------------------------ hillmic's Profile: http://www.excelforum.com/member.php...o&userid=24651 View this thread: http://www.excelforum.com/showthread...hreadid=382350 |
#5
|
|||
|
|||
I have no idea why... but it works! Thank you so much. I'm curious why the indirect text argument will work in sumproduct but wouldn't work in sum. Domenic Wrote: Try... =SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT("501:"&C3) )&"'!X7"))) Hope this helps! In article , hillmic wrote: Every week a new worksheet ("tab") is made by copying the prior week's tab and adding pertinent data for the week. Several of the cells in each "tab" are a summation across all prior worksheets/tabs, giving Year-To-Date totals. I figured out how to do this by clicking the leftmost "tab" and then shift-clicking the rightmost tab. My issue: I'd like for this to automatically happen when I create a new tab, but I get #REF's for all these references as soon as I make the copy. I've got an idea for a workaround, but I can't get it to work: 1) Put a cell in each "tab" with the name of the "tab"... e.g. cell C3 has "525" in it. 2) In the year-to-date summation cells which normally would have a formula like: =SUM('501:525'!X7) I've tried putting =SUM('501:C3'!X7). Didn't work. I've tried putting =SUM('501:"C3"'!X7) thinking maybe the quotes would let Excel know I'm trying to insert a "text string" where the sheetname/tab would normally be. Didn't work. Basically, I'm asking if there's a way to force a sheetname into a formula by making a reference to a cell with that sheetname in it? Or does anyone know a better workaround for what I'm trying to accomplish? Right now, I just manually go in and redo all the #REF's... no big deal but my curiosity has got the better of me. Thanks -- hillmic ------------------------------------------------------------------------ hillmic's Profile: http://www.excelforum.com/member.php...o&userid=24651 View this thread: http://www.excelforum.com/showthread...hreadid=382350 |
#6
|
|||
|
|||
In article ,
hillmic wrote: I have no idea why... but it works! Thank you so much. I'm curious why the indirect text argument will work in sumproduct but wouldn't work in sum. Good question! I wish I knew! :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying multiple sheets from one book 2 another and undertake spec | Excel Discussion (Misc queries) | |||
Summing same cell/cells from multiple sheets | Excel Worksheet Functions | |||
Multiple sheets selected | Excel Discussion (Misc queries) | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) | |||
CountIF across multiple sheets in a workbook | Excel Worksheet Functions |