Finding "links to other data sources"

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

In an effort not to re-create the wheel for a new
workbook, I copied materlial from old workbook to a new
workbook. The new workbook should be a stand alone with
no links to data in other workbooks. However, every time
I open the new workbook I get "This workbook contains
links to other workbooks" message.

Is there an easy way to find the data link without
scrolling through every cell in every worksheet?

Thank you.
 
Check out Power Utility Pak v5.0 (PUP v5.0) at
www.j-walk.com

It has auditng tools in the menu and select workbook linnk finder.
This tool, alone, has saved me countless hours of hunting!

Overall, the utility is GREAT!

Hope this helps!

soopia
 
I had the same problem. Do a find and type in *.xls. This will find any direct links. Also, go to your insert/name on the tool bar. A lot of inherited wkshts have build in ranges that refer to the old workbook

I've cleaned most of mine up with the above, but there are still some sheets that have them that can't be found, especially Hyperion application downloads

----- Jeff wrote: ----

In an effort not to re-create the wheel for a new
workbook, I copied materlial from old workbook to a new
workbook. The new workbook should be a stand alone with
no links to data in other workbooks. However, every time
I open the new workbook I get "This workbook contains
links to other workbooks" message.

Is there an easy way to find the data link without
scrolling through every cell in every worksheet

Thank you
 
I assume you use Excel 97. Excel 2000 has a Links submenu in the Edit menu to change/delete them. If you use Excel 97 then take the steps outlined below:

I assume that the links are not important if you are unable to tie back to them. If you wish to delete them, the easiest way to do this is to click on the first tab; hold down the Shift key while selecting the last tab in the workbook (all tabs should turn from grey to white to indicate that all are selected); go to the first tab and select the entire sheet (Ctrl+A or click the upper left-most "square" between the "A" column and row 1); Copy-->Paste Special-->Values. Close the workbook, re-open it and it should open without that annoying dialog box.

Here are some alternative methods:

1.) Check the formulas in the workbook. Select Edit-->Find and try to find [. The square bracket is usually a good indicator of an external link (link to another workbook).

2.) If the workbook has charts, then check the "series" formula for each one for external links.

3.) Select Insert-->Name-->Define and scroll through the list. Check the "refers to" box for each name to see if it refers to an external file. If you use Excel 97 then get the sheet/column/row"address" in the workbook to where the external link points to Copy-->Paste Special-->Values to erase the link.

EU

----- Gene wrote: -----

I had the same problem. Do a find and type in *.xls. This will find any direct links. Also, go to your insert/name on the tool bar. A lot of inherited wkshts have build in ranges that refer to the old workbook.

I've cleaned most of mine up with the above, but there are still some sheets that have them that can't be found, especially Hyperion application downloads.

----- Jeff wrote: -----

In an effort not to re-create the wheel for a new
workbook, I copied materlial from old workbook to a new
workbook. The new workbook should be a stand alone with
no links to data in other workbooks. However, every time
I open the new workbook I get "This workbook contains
links to other workbooks" message.

Is there an easy way to find the data link without
scrolling through every cell in every worksheet?

Thank you.
 
Go to EDIT then LINKS. If you dont see it there then you probabl
either have a NAME referencing the old sheet. If this is the case g
to INSERT/NAMES/DEFINE and delete the name. If you stilll cant fin
the link you probably have a macro referencing teh old sheet. I wor
with hundreds of linked sheets so I have an add-in that finds an
delete links no matter how buried they are in the file. There are lot
of free one out ther
 
I saw your reply but do not see the add-in downloaded.
Can you tell me where I can find the download to remove
the links? I have external links and macros I want to
remove when I "save as" so it doesn't continue to search
for the data.

Thanks.
 
Back
Top