Hello!

I have a workbook with five sheets (left to right) named
with text names, then a sheet named AREA, followed by
various sheets whose tabs(names) are numbers. The AREA
sheet is a summary sheet and consists of many formulas
such as SUM('16:56'!E6), referring to the sum of E6 in
Sheets 16, 23, 25, 20 and 56.

I need to copy this workbook about 50 times and plug in
numbered sheet tabs that are different than the original
workbook (all text-named sheets remain the same).

The thought just occurred to me that I am going to have to
physically change each formula to correspond to the new
workbook's new sheet numbers! Yikes!

Is there a more generic way I can write a formula so
that it will, for instance, sum the cells in all the
sheets after "AREA" or some kind of code that can be used
in conjunction with the formula that only takes a cell
value from the numbered worksheets ... or something?

Any help will be greatly appreciated!

Sandy

Hi Sandy,

There is a very good technique for this kind of problem, but it only
works if all of your formulas refer to all of the numbered sheets. Add an
empty worksheet right in front of the first numbered worksheet. Give this
worksheet the sheet tab name First. Add another empty worksheet after the
very last numbered worksheet and name it Last.

Once you've done this you can change all of the formulas on your AREA
sheet to look like this:

=SUM(First:Last!A1)

This method allows you to rename, add, and remove any of the worksheets
between First and Last without having to update the formula. You can hide
these two sheets so they don't affect the appearance of the workbook. Just
make sure that they stay blank so they don't change the values of the
formulas.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Sandy" wrote in message
...
Hello!

I have a workbook with five sheets (left to right) named
with text names, then a sheet named AREA, followed by
various sheets whose tabs(names) are numbers. The AREA
sheet is a summary sheet and consists of many formulas
such as SUM('16:56'!E6), referring to the sum of E6 in
Sheets 16, 23, 25, 20 and 56.

I need to copy this workbook about 50 times and plug in
numbered sheet tabs that are different than the original
workbook (all text-named sheets remain the same).

The thought just occurred to me that I am going to have to
physically change each formula to correspond to the new
workbook's new sheet numbers! Yikes!

Is there a more generic way I can write a formula so
that it will, for instance, sum the cells in all the
sheets after "AREA" or some kind of code that can be used
in conjunction with the formula that only takes a cell
value from the numbered worksheets ... or something?

Any help will be greatly appreciated!

Sandy

Hi Rob,

Cool! Thank you so much!

Sandy

-----Original Message-----
Hi Sandy,

There is a very good technique for this kind of
problem, but it only
works if all of your formulas refer to all of the
numbered sheets. Add an
empty worksheet right in front of the first numbered
worksheet. Give this
worksheet the sheet tab name First. Add another empty
worksheet after the
very last numbered worksheet and name it Last.

Once you've done this you can change all of the
formulas on your AREA
sheet to look like this:

=SUM(First:Last!A1)

This method allows you to rename, add, and remove any of
the worksheets
between First and Last without having to update the
formula. You can hide
these two sheets so they don't affect the appearance of
the workbook. Just
make sure that they stay blank so they don't change the
values of the
formulas.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Sandy" wrote in message
...
Hello!

I have a workbook with five sheets (left to right) named
with text names, then a sheet named AREA, followed by
various sheets whose tabs(names) are numbers. The AREA
sheet is a summary sheet and consists of many formulas
such as SUM('16:56'!E6), referring to the sum of E6 in
Sheets 16, 23, 25, 20 and 56.

I need to copy this workbook about 50 times and plug in
numbered sheet tabs that are different than the original
workbook (all text-named sheets remain the same).

The thought just occurred to me that I am going to have
to
physically change each formula to correspond to the new
workbook's new sheet numbers! Yikes!

Is there a more generic way I can write a formula so
that it will, for instance, sum the cells in all the
sheets after "AREA" or some kind of code that can be
used
in conjunction with the formula that only takes a cell
value from the numbered worksheets ... or something?

Any help will be greatly appreciated!

Sandy




.