Ken -

Thanks -

I'll consider Indirect. It's volitile, and I won't like it, but it may
be the best way to go.

Thanks again.
....best, Hash

In article ,
"Ken Wright" wrote:

But if there's nothing that says it absolutely has to be the second sheet
then my advice was going to be exactly the same as Rob's. I'd name the
second sheet 'start' and the third 'finish' or maybe just a and b, and then
use as Rob suggested

=SUM(start:finish!A1)
or
=SUM(a:b!A1) instead of references to a sheet2 or whatever the name would be

Then just drag your new sheet each time between a and b and drag the old one
out to the right of sheet b. Sheet1 will then only pick up data from any
sheet between a and b. There will be no data on sheets a or b so all you
will get is the data from your new sheet.

Failing that take a look at the INDIRECT function and then in a single cell
on your summary sheet put the name of the tab you are referring to. Use
that cell in your formulas and then just change the sheet name in that one
cell to refer to the one you want, eg if the name of your second sheet was
in cell C1 on your summary sheet then you could use

=INDIRECT(C1&"!A1")

on your summary sheet to refer to cell A1 on whatever sheet has it's name in
cell C1

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

wrote in message news:bMEEd.13008$B95.2034@lakeread02...
Rob -

The problem is that there is no "between" involved. It's always the
second tab, and every quarter it has a new name. To invent some syntax,
I'd want something like

A1: = sheet[+1]!A1

from RC notation.

...best, Hash


In article ,
"Rob" wrote:

Hash,

If the following was the formula in say cell A1 of Sheet1 (your
summary),
then inserting a new sheet between Sheet2 and Sheet3 would automatically
be
included in the summary sheet (Sheet1).

=SUM(Sheet2:Sheet3!A1)

Regards, Rob

wrote in message
...
Gang -

I have a workbook with a summary worksheet at the first tab, and then
a
new worksheet inserted every quarter as the second tab. Earlier
quarters
are pushed to the right on the tabs.

Is there a way to reference data on the second tab no matter what that
second worksheet is titled? It changes every quarter. Right now I do
a
global replace manually via menu.


Thanks in advance.

...best, Hash