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
> >
>
>