Edit Links Feature (and a solution to a potential problem)

Excel contains a wonderful tool to help with links between spreadsheets. It is located under the EDIT tab in Excel 2003 and the DATA tab in Excel 2007 and is called LINKS. You can see how to use it on the EDIT LINKS VIDEO tutorial.

However, one of my clients recently came across a problem. Where there are lots of links (literally thousands) the tool doesn’t always do what it implies. So if you have say a spreadsheet that links to 5 other spreadsheets and all 5 links have now changed. When you activate the EDIT LINKS and CHANGE SOURCE for one of the spreadsheets, it typically asked you for the location of the other spreadsheets immediately (especially where the reference to all the external spreadsheets are contained in a single cell).

This is not always reliable however and in fact it often refers to the incorrect location even though you have specified the exact location. The immediate solution is that, even if Excel asks for the location of all spreadsheets when you change source for the first one, you must still go to each link, click on Change Source and show it the location of each spreadsheet individually.