Many times when building spreadsheets, I have a set of formulae that do what I want in one workbook that I want to copy to a similar workbook with the same worksheet names.
If you've tried to do this, you will know it is not as straightforward as it sounds.
For example, if I have 5 sales reporting spreadsheets in the same format for 5 group companies, I might have an invoice layout in one worksheet (let's call this sheet Invoice) that pulls information from a list of sales invoices on another worksheet (called Data).
If I decide to redesign the layout of all invoices to follow a new format across the group, it would make sense to do this once and copy it to the other 4 when I am happy.
When I copy the new layout and paste it into the Invoice sheet of one of the other companies, all of the formulae automatically link to the Data sheet in the first spreadsheet by adding the file path to the cell references (not what I want).
Where I could approach this with some careful use of Find and Replace, there is a quicker, simpler way that will address the problem for the whole workbook.
Excel stores the names and locations of any spreadsheets that it links to in a table that you can access by clicking "Edit Links" on the Data ribbon (in Excel 2003 or earlier, choose "Links..." from the Edit menu).
Click on the name of the external worksheet in the list of sources (unless you have other links to external spreadsheets in this workbook, it will be the only one there).
Click the "Change Source" button on the right and browse to the location of the new workbook (the one that you are in) and click OK.
The link will disappear from the list as it is no longer an external link - all formulae are now referring to the corresponding location in the new workbook, rather than to an external workbook.
You can check your formulae now and the file paths will have disappeared.
This becomes even more useful when you are copying many formulae in multiple sheets across from one workbook to another - as this process redirects every reference to the original sheet to the new one.
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".
Thank you, Thank you, Thank you!!!! I have been looking for a way to fix copying charts from one workbook to another and correct the linking to the new workbook. this works perfectly for what I needed.
ReplyDeleteThanks for the help
ReplyDeleteThank you. Very useful information.
ReplyDeleteThank you. Your tip is very helpful.
ReplyDeleteThanks, very simple fix. So useful to me.
ReplyDeleteThis worked well. Thanks!!
ReplyDeleteI don't have the "change source" available? It's greyed out and cant be accessed?
ReplyDeleteHave you selected the source spreadsheet on the list first? It will be greyed out until you choose the source that you want to change - even if there is only one on the list.
Delete