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

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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
>
> 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.1r97qd_1119823506.5583@excelforum-nospam.com>,
hillmic <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OV3zy5peFHA.3040@TK2MSFTNGP14.phx.gbl...
> 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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
> message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
> >
> > 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.

Originally Posted by Domenic

Try...

=SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT("501:"&C3))&"'!X7")))

Hope this helps!

In article <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com>,
hillmic <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> 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

In article <hillmic.1raiyq_1119884758.4295@excelforum-nospam.com>,
hillmic <hillmic.1raiyq_1119884758.4295@excelforum-nospam.com> 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!

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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
>
> 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.1r97qd_1119823506.5583@excelforum-nospam.com>,
hillmic <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OV3zy5peFHA.3040@TK2MSFTNGP14.phx.gbl...
> 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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
> message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
> >
> > 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.1raiyq_1119884758.4295@excelforum-nospam.com>,
hillmic <hillmic.1raiyq_1119884758.4295@excelforum-nospam.com> 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!

Try...

=SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT("501:"&C3))&"'!X7")))

Hope this helps!

In article <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com>,
hillmic <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> 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

In article <hillmic.1raiyq_1119884758.4295@excelforum-nospam.com>,
hillmic <hillmic.1raiyq_1119884758.4295@excelforum-nospam.com> 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!

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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OV3zy5peFHA.3040@TK2MSFTNGP14.phx.gbl...
> 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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
> message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
> >
> > 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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
>
> 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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
>
> 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.1r97qd_1119823506.5583@excelforum-nospam.com>,
hillmic <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OV3zy5peFHA.3040@TK2MSFTNGP14.phx.gbl...
> 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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
> message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
> >
> > 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.1raiyq_1119884758.4295@excelforum-nospam.com>,
hillmic <hillmic.1raiyq_1119884758.4295@excelforum-nospam.com> 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!

In article <hillmic.1raiyq_1119884758.4295@excelforum-nospam.com>,
hillmic <hillmic.1raiyq_1119884758.4295@excelforum-nospam.com> 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!

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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
>
> 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.1r97qd_1119823506.5583@excelforum-nospam.com>,
hillmic <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OV3zy5peFHA.3040@TK2MSFTNGP14.phx.gbl...
> 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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
> message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
> >
> > 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.1raiyq_1119884758.4295@excelforum-nospam.com>,
hillmic <hillmic.1raiyq_1119884758.4295@excelforum-nospam.com> 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!

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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OV3zy5peFHA.3040@TK2MSFTNGP14.phx.gbl...
> 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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
> message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
> >
> > 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.1r97qd_1119823506.5583@excelforum-nospam.com>,
hillmic <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> 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

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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
>
> 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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
>
> 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.1raiyq_1119884758.4295@excelforum-nospam.com>,
hillmic <hillmic.1raiyq_1119884758.4295@excelforum-nospam.com> 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!

Try...

=SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT("501:"&C3))&"'!X7")))

Hope this helps!

In article <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com>,
hillmic <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OV3zy5peFHA.3040@TK2MSFTNGP14.phx.gbl...
> 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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
> message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
> >
> > 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.1raiyq_1119884758.4295@excelforum-nospam.com>,
hillmic <hillmic.1raiyq_1119884758.4295@excelforum-nospam.com> 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!

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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
>
> 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.1r97qd_1119823506.5583@excelforum-nospam.com>,
hillmic <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OV3zy5peFHA.3040@TK2MSFTNGP14.phx.gbl...
> 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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
> message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
> >
> > 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.1raiyq_1119884758.4295@excelforum-nospam.com>,
hillmic <hillmic.1raiyq_1119884758.4295@excelforum-nospam.com> 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!

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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OV3zy5peFHA.3040@TK2MSFTNGP14.phx.gbl...
> 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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
> message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
> >
> > 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.1r97qd_1119823506.5583@excelforum-nospam.com>,
hillmic <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> 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

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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
>
> 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.1r97qd_1119823506.5583@excelforum-nospam.com>,
hillmic <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> 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

In article <hillmic.1raiyq_1119884758.4295@excelforum-nospam.com>,
hillmic <hillmic.1raiyq_1119884758.4295@excelforum-nospam.com> 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!

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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
>
> 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
>

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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OV3zy5peFHA.3040@TK2MSFTNGP14.phx.gbl...
> 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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
> message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
> >
> > 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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
>
> 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.1r97qd_1119823506.5583@excelforum-nospam.com>,
hillmic <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OV3zy5peFHA.3040@TK2MSFTNGP14.phx.gbl...
> 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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
> message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
> >
> > 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.1raiyq_1119884758.4295@excelforum-nospam.com>,
hillmic <hillmic.1raiyq_1119884758.4295@excelforum-nospam.com> 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!

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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
>
> 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.1r97qd_1119823506.5583@excelforum-nospam.com>,
hillmic <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OV3zy5peFHA.3040@TK2MSFTNGP14.phx.gbl...
> 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" <hillmic.1r97qd_1119823506.5583@excelforum-nospam.com> wrote in
> message news:hillmic.1r97qd_1119823506.5583@excelforum-nospam.com...
> >
> > 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.1raiyq_1119884758.4295@excelforum-nospam.com>,
hillmic <hillmic.1raiyq_1119884758.4295@excelforum-nospam.com> 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!