Checking Dependence Before Deleting Worksheet

  • Thread starter Thread starter excel wonk
  • Start date Start date
E

excel wonk

Before deleting any worksheet tab, I want to find out if there's
anything that affects other worksheets. Any way to check for this
short of clicking every cell and using "Trace Dependents"?

I know the spreadsheet gives message that deletion may affect other
data, continue?

Any way to check this AND find out which cells are dependents used in
other worksheets? Thanks.
 
Hi,

There is not built in command to do this automatically for all sheets. You
can do it manually for each sheet at a time by using the Edit,Find command
and searching for something like =Sheet1! The real problem with this
approach is that there are other things than simple formulas that may
reference a sheet - charts, and range names, for example.

You could write a VBA macro.

You could also Google this topic, I'm sure someone has created an add-in to
do this or there is code available.
 
One thing I do is to move (not copy) the worksheet to a new workbook. Then I
look for links to that new workbook.

I'd get a couple of tools to help find those links:

Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm

And Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

=====
Note that this isn't 100% effective. It doesn't do anything with any VBA code
that could be relying on that worksheet.
 
Back
Top