Formula that automatically picks up a cell in a new sheet???

I am calculating the average value of the same cell in various sheets in the workbook.
However I would like the formula to pick up a new cell when a new sheet is generated automatically. Also it is likely that the sheet will be renamed, which could also cause problems.

For example, the formula takes the max value of cell A1 in sheets 1,2,3 but when sheet 4 is added cell A1 in sheet 4 will not be picked up.

Thanks, your quick responses will be appreciated.
Arfan

Hi arfanvilla,

This isn't easy to do without VBA but I'll show you one option using an example. It's a bit hard to explain so sorry for the long reply...,.

Scenario
Say we have a new workbook with 3 worksheets (Sheet1,Sheet2,Sheet3).

Now say you want a formula in cell A2 on Sheet1 which returns the MAX value from the Cell A1 from each of these sheets. But, if an extra sheet is added you also want A1 from that new sheet to be taken into account.

Solution

• Add two more worksheets to the workbook - one at the beginning and one at the end. So now your worksheet order is (Sheet4,Sheet1,Sheet2,Sheet3,Sheet5).
• Next, set up a 3-D named range as follows:
• Insert-->Name-->Define. Give the range a name, say, A1_ALLSHEETS. In the refers to formula box type in this: ='Sheet4:Sheet5'!$A$1 and click OK.
• Now hide sheet4 and sheet5 by selecting each sheet and Format-->Sheet-->Hide.

Testing
Now let's test what we've got.
Type in these values:

Please Login or Register  to view this content.

Now, on sheet1 in cell A2 type in the following formula:
=MAX(A1_ALLSHEETS)
It returns 90 which is correct.

Now, insert a new sheet. Add a value of 120 to cell A1 on it. Now go back and check the formula you typed into cell A2 on Sheet1. You'll see it's updated to 120 also.

Explanation
Effectively we're using the two hidden sheets to act as buffers which encapsulate the sheets which you want to be considered. When you add a new sheet it will be added between these 'buffers' which means that it will be included within the formula's calculations.

I hope that makes sense.

Colin

Your reply was perfect, thank you very much.
i'll try and adapt your solution to my current spreadsheet - but it shoud work fine now, cheers.