Excel 2010 – Chasing Links to External Worksheets

[stevenfletcher]

I have a number of spreadsheets which are similar and so I copied one from another. I had the same problem that many people have had about links to previous spreadsheets (the ones I copied from). I searched the internet many times and looked to solve the problem like searching for “ [ “ and downloaded Micro Soft’s wizard for deleting links. None of it helped and I could not break or locate the links. I finally traced it down to conditional formatting. I had 5 tabs and I added them (copy all) from another worksheet. When I got to one which had conditional formatting the problem of being unable to find and break links started to appear. I deleted column by column and eventually found a column contain week numbers that had conditional formatting to highlight the week number if it was the week under editing. I removed the conditional formatting and the problem was solved. I have not yet found out how to copy this without creating unwanted link but at least I found the problem and the worst case solution I have to create the formula for highlighting the cells each time. Does anyone know how to copy the conditional formatting without creating these links?

 

[Andrew Poulsom]

What's the Conditional Formatting formula?

 

[stevenfletcher]

='C:\Users\Steven Fletcher\Desktop\[190-Fri-1430-EngCornerJiuQuanPrimary.xlsx]LP'!#REF! = #REF!
So the link was broken and this link got copied every time I created a new spreadsheet based on the old one. I have now discovered other places where a link to an external file (in VLOOKUP) go copied. I "believe" originally in was a reference to another tab in the same worksheet. Is there a way to insure that such links are to local tabs?

 

[Andrew Poulsom]

If you copy a worksheet that references another worksheet in the same workbook to a new workbook, a link to the original workbook will be created. The solution is to simultaneously copy both source and target worksheets.