Remove a worksheet, without causing a reference error?

[Smokeyham]

Working in Excel 2007, I have a spreadsheet which summarizes data from several sheets (all contained within the same file).

When I delete one of the sheets, I get a reference error on the summary sheet:

=#REF!A1+Sheet2!A1

Is there a way to delete a sheet, and have the reference automatically deleted from the summary spredsheet?

Thanks for any help.

 

[diddi]

not that i know of. you will need to find the instances and remove the references to the deleted sheet. it is most likely possible the write a macro that edits the formulea in the cells but the effort to write may outweigh the effort to edit the sheet.

 

[Smokeyham]

not that i know of. you will need to find the instances and remove the references to the deleted sheet. it is most likely possible the write a macro that edits the formulea in the cells but the effort to write may outweigh the effort to edit the sheet.

Arrrgghhh! That is what I was afraid of. You would think that adding and removing sheets would be a regular occurance, and that the formulas could automatically update to at least reflect the removal of a sheet.

 

[dave3009]

If you had a static first sheet and a static last sheet that you were referencing AND you were just summing one cell then you could spear the sheets with a sum

=SUM(Sheet1:Sheet30!A1)

Any sheets that were between Sheet1 and Sheet30 that were added or deleted would get included in the sum

 

[Smokeyham]

If you had a static first sheet and a static last sheet that you were referencing AND you were just summing one cell then you could spear the sheets with a sum

=SUM(Sheet1:Sheet30!A1)

Any sheets that were between Sheet1 and Sheet30 that were added or deleted would get included in the sum

Interesting solution! Unfortunately, we have named the sheets with different names such as "71 - Forestry," and "Resource Management."

 

[dave3009]

I think you missed what I was meaning. Try this:

Create two completely blank sheets, name one First, name the other Last.

Put the First sheet before all the sheets you want to sum the cell on

Put the Last sheet After all the sheets you want to sum the cell on

Now right click the tabs on each of these sheets and hide them.

BE SURE THE SUMMARY SHEET IS NOT BETWEEN THESE SHEETS

Now in Summary sheet enter formula =SUM(First:Last!A1)

This will return the sum of all cells A1 in the sheets between first and last, but as first and last are blank they will not get included. Now delete a sheet, you will note the cells of A1 of the remaining sheets are being summed without error.

One caveat...when you add sheets you will need to unhide at least one of the sheets to ensure you place the new sheet in between First and Last. A small price to pay to save updating lots of REF errors

 

[Smokeyham]

I think you missed what I was meaning. Try this:

Create two completely blank sheets, name one First, name the other Last.

Put the First sheet before all the sheets you want to sum the cell on

Put the Last sheet After all the sheets you want to sum the cell on

Now right click the tabs on each of these sheets and hide them.

BE SURE THE SUMMARY SHEET IS NOT BETWEEN THESE SHEETS

Now in Summary sheet enter formula =SUM(First:Last!A1)

This will return the sum of all cells A1 in the sheets between first and last, but as first and last are blank they will not get included. Now delete a sheet, you will note the cells of A1 of the remaining sheets are being summed without error.

One caveat...when you add sheets you will need to unhide at least one of the sheets to ensure you place the new sheet in between First and Last. A small price to pay to save updating lots of REF errors

I did misunderstand you, and your solution worked perfectly!! Thank you so much - You saved me hours of manual labour

 

[dave3009]

You're very welcome, thanks for leaving feedback