variable across all worksheets?
 

Is there a variable to span the same cell over all worksheets?

Heres what I got going on:

I do a weekly inventory at my job. Each weeks inventory is on a seperate
worksheet (they are identical copies of the previous, but the name of the
sheet is renamed to the date inventory was done). I added a new worksheet
at the beginning that would give an average (weekly) of the usage.

Since "H9" contains the same relevent data across all the worksheets, and
since new worksheets are added each week, is there a simple way to do this,
without having to change the formula each week to incorporate the new sheets
name?

I guess instead of
=AVERAGE('7-29-05'!H9,'08-05-05'!H9,'08-12-05'!H9)
is there a way to do
=AVERAGE(allSheetsAndAllFutureSheets!H9)

also, some of these cells will have a null value, is there a way to do the
average without getting an error from the null?

Joe Quigley

You could insert two new sheets, name them First and Last, and put all
the relevant sheets between the new sheets which you can hide... The
formula becomes:

=AVERAGE(First:Last!H9)

Joe wrote:
Is there a variable to span the same cell over all worksheets?

Heres what I got going on:

I do a weekly inventory at my job. Each weeks inventory is on a seperate
worksheet (they are identical copies of the previous, but the name of the
sheet is renamed to the date inventory was done). I added a new worksheet
at the beginning that would give an average (weekly) of the usage.

Since "H9" contains the same relevent data across all the worksheets, and
since new worksheets are added each week, is there a simple way to do this,
without having to change the formula each week to incorporate the new sheets
name?

I guess instead of
=AVERAGE('7-29-05'!H9,'08-05-05'!H9,'08-12-05'!H9)
is there a way to do
=AVERAGE(allSheetsAndAllFutureSheets!H9)

also, some of these cells will have a null value, is there a way to do the
average without getting an error from the null?

Joe Quigley