Remove a worksheet, without causing a reference error?

Smokeyham

Board Regular
Joined
Feb 1, 2006
Messages
119
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Feb 1, 2006
Messages
119
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,079
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
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

Board Regular
Joined
Feb 1, 2006
Messages
119
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,079
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
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

Board Regular
Joined
Feb 1, 2006
Messages
119
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,079
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
You're very welcome, thanks for leaving feedback :)
 

Forum statistics

Threads
1,172,227
Messages
5,879,810
Members
433,457
Latest member
destinatus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top