LinkBack URL
About LinkBacksTry
=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
>
Register To Reply
Bookmarks