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

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

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

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

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

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! :)