Master Button to Delete All Column Formula Links on a Sheet

SYSPK

Is there a way to delete all the cell links on a sheet in 1 go? We have about 20 columns used in SUMIFS formulas on another sheet. One day all of those formulas showed INVALID and BLOCKED. The activity history of the source sheet said that one of our users "removed 19 cross-sheet references" but there's no further information. The person said that they didn't do anything differently that day.

Any idea of how this could have happened?

Paul Newcome

Not if you delete it from the source sheet. If you select the option on the source sheet, it will remove ALL cross sheet references.

In comparing the 1st and 3rd screenshots, you can see that the references were removed from both columns on the sheet even though they were separate references and I had only selected the delete option on one of them.

When going back to the Test 2 sheet where the formulas were that contained the cross sheet references, all formulas were broken including those with ranges outside of the cell being referenced that was used to delete them.

Paul Newcome

It is possible to remove the links from both sides in a couple of different ways. There is the way shown in your screenshot which can be done from both the target and source sheets, and you can also use the "Manage References" feature when you access the cell options.

SYSPK

I doubt that was the case here because they also would have had to select each reference and delete them.

Thank you though.

Paul Newcome

Not if you delete it from the source sheet. If you select the option on the source sheet, it will remove ALL cross sheet references.

In comparing the 1st and 3rd screenshots, you can see that the references were removed from both columns on the sheet even though they were separate references and I had only selected the delete option on one of them.

When going back to the Test 2 sheet where the formulas were that contained the cross sheet references, all formulas were broken including those with ranges outside of the cell being referenced that was used to delete them.

SYSPK

Got it. Thanks for explaining.

Paul Newcome

Happy to help. 👍️