MrExcel Publishing
Your One Stop for Excel Tips & Solutions

References to Linked Workbooks - How do you find?


Posted by Ed J. on February 06, 2001 2:28 PM

When opening a large workbook (book1), I get the message that it is linked to another workbook(book2). The book it tries to reference (book2) does not exist and Excel takes me into 'Open' mode to find the workbook. I cancel and try to find the reference to book2 by using the 'Find' with formulas checked in all the sheets in book1. I can't find the reference in any of the sheets.

Is there an easier way to find cells that have linked references?


Posted by Scott R on February 06, 2001 2:58 PM

Did you check for external references in the Refers To box of each sheet's defined names?

Posted by Dave Hawley on February 06, 2001 3:43 PM

Hi Ed

This is a common problem with Excel and there are a few ways to fix it.

Try these steps:

1. Go to Insert>Name>Define and make sure you do not have any named ranges refering to an outside Workbook.

2. Go to Edit>Links and try to use the "Change Source" button to refer your "PR" link back to the your open workbook. In other words try and change the link so it refers to itself.

3. Open the a new workbook and create a link to it and Save. Now go to Edit>Links and use the "Change Source" to refer the "PR" link to the new Workbook. Save again and then delete the link you created.

.....If all the above fail, microsoft have seen this as a problem and have a free download here:

http://support.microsoft.com/support/kb/articles/Q188/4/49.ASP?LN=EN-US&SD=gn&FR=0&qry=delete%20links&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=XLW97

In fact it is a handy add-in to have even if one of the above steps does work.

Hope this helps

Dave

OzGrid Business Applications

Posted by Dave Hawley on February 06, 2001 3:44 PM