Calculate formula while inserting and removing sheets

[apr371]

Hi,

I need to calculate formula in sheet1 taking values from others sheets.
For example I have a formula in sheet1 containing the cell A1 of others sheets (sheet2,sheet3,sheet4 only). If i remove any one sheet (ex. sheet3), then the formula should work taking remaining sheets (ie., sheet2, sheet4) and also when i insert a worksheet with the name sheet3. How can i achieve this? Can I use reference only or VBA / macro? Can any one help to sort out this problem? Thanks

 

[Yard]

You can use:

=SUM(Sheet2:Sheet4!A1)

 

[DonkeyOte]

Welcome to the Board

just extending Yard's solution... if it's possible that either Sheet2 or Sheet4 may be deleted and replaced I would personally go for the insertion of 2 sheets... one called START and one called END... START would be placed before the first sheet to SUM and END placed after the last sheet to Sum (ie using present example you would have START,Sheet2,Sheet3,Sheet4,END) -- your formula on sheet1 thus becomes:

=SUM(START:END!A1)

Then even if either of Sheet2 / Sheet4 are removed the formula on Sheet1 is unaffected... ie the key is that the sheet "boundaries" (eg START/END) should be sheets that are never going to be removed.