Need formula

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

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 new sheet numbers! Yikes!

Is there a more generic way I can write the formula so
that it will, for instance, sum the cells in all the
sheets after "AREA"?

Any help will be greatly appreciated!

Sandy
 
Sandy,

One way would be to keep the sheet names in a table in the
AREA sheet (or elsewhere) and have your formulas refer to
that table to get the sheet names.

Hope that helps and is not too vague.
Ryan
 
...
...
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 new sheet numbers! Yikes!
Click to expand...
...

If all these other workbooks are designed as copies of the original worksheet,
then when you load a copy and change its worksheet names, all the formulas that
refer to these worksheets *directly* will update automatically. For example, if
workbook first.xls contains a worksheet named '1', and another worksheet in it
contains a formula like ='1'!X99, then if you copy first.xls to second.xls, open
second.xls and rename worksheet '1' as '9', the formula mentioned will
automatically change to ='9'!X99.

On the other hand, if you're summing over all sheets aside from a summary
worksheet (such as your AREA worksheet), it's generally wise to create two blank
worksheets (I use the names alpha and omega, others use first and last) that
bracket the data worksheets, then use formulas like =SUM('alpha:omega'!B5) to
sum over all data worksheets.
 
Back
Top