I have a financial model with some 15-20 worksheets. I dont need a lot
of these work sheets and would ideally like to delelte them but before
that i want to ensure that no cell references to other worksheets i.e.
cells in other worksheets are not dependent on the to-be-deleted sheet.

I have tried CtrlG>Alt S>dependent cells
Formula Auditing tool

but these are too tedious when i have too many cells filled in every
worksheet.

Any other way to ensure that the sheet has no dependents?

Tks/Amag
Ps: tried
http://groups.google.com/group/micro...b64de66473889a
cudn't figure out?

Amag

Download Bill Manville's FINDLINK.XLA from Stephen Bullen's site.

http://www.oaltd.co.uk/Default.htm

Click on link to MVP's

With this add-in you can find and delete links if any exist.


Gord Dibben Excel MVP


On 26 Nov 2005 10:06:33 -0800, "amag" <(E-Mail Removed)> wrote:

>I have a financial model with some 15-20 worksheets. I dont need a lot
>of these work sheets and would ideally like to delelte them but before
>that i want to ensure that no cell references to other worksheets i.e.
>cells in other worksheets are not dependent on the to-be-deleted sheet.
>
>I have tried CtrlG>Alt S>dependent cells
>Formula Auditing tool
>
>but these are too tedious when i have too many cells filled in every
>worksheet.
>
>Any other way to ensure that the sheet has no dependents?
>
>Tks/Amag
>Ps: tried
>http://groups.google.com/group/micro...b64de66473889a
>cudn't figure out?

The add-in gives a list of links for the complete xls file. However,
what i am looking for is -I have to delete a worksheet, which i can do
only if some other worksheet is not linked with the current worksheet?
Let us say there are three worksheets sheetA,sheetB,sheetC . I want to
delete SheetA if and only if nothing in sheetB and sheetC is dependent
on sheetA contents?

TIA / Amag

Gord Dibben wrote:
> Amag
>
> Download Bill Manville's FINDLINK.XLA from Stephen Bullen's site.
>
> http://www.oaltd.co.uk/Default.htm
>
> Click on link to MVP's
>
> With this add-in you can find and delete links if any exist.
>
>
> Gord Dibben Excel MVP
>
>
> On 26 Nov 2005 10:06:33 -0800, "amag" <(E-Mail Removed)> wrote:
>
> >I have a financial model with some 15-20 worksheets. I dont need a lot
> >of these work sheets and would ideally like to delelte them but before
> >that i want to ensure that no cell references to other worksheets i.e.
> >cells in other worksheets are not dependent on the to-be-deleted sheet.
> >
> >I have tried CtrlG>Alt S>dependent cells
> >Formula Auditing tool
> >
> >but these are too tedious when i have too many cells filled in every
> >worksheet.
> >
> >Any other way to ensure that the sheet has no dependents?
> >
> >Tks/Amag
> >Ps: tried
> >http://groups.google.com/group/micro...b64de66473889a
> >cudn't figure out?

If the only links are in worksheet cells, you could search the formulas for an
exclamation point.

Select all the cells in sheetb (then sheetc, etc)
edit|goto
special|formulas
edit|find
! (or sheeta! or 'sheet a'!)

repeat as much as you need

amag wrote:
>
> The add-in gives a list of links for the complete xls file. However,
> what i am looking for is -I have to delete a worksheet, which i can do
> only if some other worksheet is not linked with the current worksheet?
> Let us say there are three worksheets sheetA,sheetB,sheetC . I want to
> delete SheetA if and only if nothing in sheetB and sheetC is dependent
> on sheetA contents?
>
> TIA / Amag
>
> Gord Dibben wrote:
> > Amag
> >
> > Download Bill Manville's FINDLINK.XLA from Stephen Bullen's site.
> >
> > http://www.oaltd.co.uk/Default.htm
> >
> > Click on link to MVP's
> >
> > With this add-in you can find and delete links if any exist.
> >
> >
> > Gord Dibben Excel MVP
> >
> >
> > On 26 Nov 2005 10:06:33 -0800, "amag" <(E-Mail Removed)> wrote:
> >
> > >I have a financial model with some 15-20 worksheets. I dont need a lot
> > >of these work sheets and would ideally like to delelte them but before
> > >that i want to ensure that no cell references to other worksheets i.e.
> > >cells in other worksheets are not dependent on the to-be-deleted sheet.
> > >
> > >I have tried CtrlG>Alt S>dependent cells
> > >Formula Auditing tool
> > >
> > >but these are too tedious when i have too many cells filled in every
> > >worksheet.
> > >
> > >Any other way to ensure that the sheet has no dependents?
> > >
> > >Tks/Amag
> > >Ps: tried
> > >http://groups.google.com/group/micro...b64de66473889a
> > >cudn't figure out?

--

Dave Peterson

Another way--but try it against a copy of your workkbook.

Select SheetA and move (not copy) it to a new workbook. Save that workbook.

Now back to the copy of that original workbook.

Use Bill Manville's FindLink.xla to see if there are any external links to that
newly saved workbook.

amag wrote:
>
> The add-in gives a list of links for the complete xls file. However,
> what i am looking for is -I have to delete a worksheet, which i can do
> only if some other worksheet is not linked with the current worksheet?
> Let us say there are three worksheets sheetA,sheetB,sheetC . I want to
> delete SheetA if and only if nothing in sheetB and sheetC is dependent
> on sheetA contents?
>
> TIA / Amag
>
> Gord Dibben wrote:
> > Amag
> >
> > Download Bill Manville's FINDLINK.XLA from Stephen Bullen's site.
> >
> > http://www.oaltd.co.uk/Default.htm
> >
> > Click on link to MVP's
> >
> > With this add-in you can find and delete links if any exist.
> >
> >
> > Gord Dibben Excel MVP
> >
> >
> > On 26 Nov 2005 10:06:33 -0800, "amag" <(E-Mail Removed)> wrote:
> >
> > >I have a financial model with some 15-20 worksheets. I dont need a lot
> > >of these work sheets and would ideally like to delelte them but before
> > >that i want to ensure that no cell references to other worksheets i.e.
> > >cells in other worksheets are not dependent on the to-be-deleted sheet.
> > >
> > >I have tried CtrlG>Alt S>dependent cells
> > >Formula Auditing tool
> > >
> > >but these are too tedious when i have too many cells filled in every
> > >worksheet.
> > >
> > >Any other way to ensure that the sheet has no dependents?
> > >
> > >Tks/Amag
> > >Ps: tried
> > >http://groups.google.com/group/micro...b64de66473889a
> > >cudn't figure out?

--

Dave Peterson

Thanks for ur responses ...doesn't really help though ...dave the work
around you suggested wud be gud only if u hv a limited no of worksheets
in ur file. However, with 15 worksheets, as i mentioned earlier, that
wud mean doing repeating the steps atleast 14 times in just one sheet!
and i have 14 more to go. tried second method also ...gud but too risky
with these many linkages ... tks anyways /Amag