Chris has attended:
Excel Advanced course
Excel Links to Other workbooks
Hi,
I have a question about links within Excel to other workbooks.
In our company we have large Excel Master Workbooks which are linked to many other workbooks/Powerpoints/ Word documents to produce reports.
However, we are looking to improve these master workbooks. Therefore we want to make a copy and edit it and play around with it to see if we can create a cleaner/more efficient workbook.
However, we are aware of the amount of important links to other workbooks these master files have. Therefore when creating a copy that we want to mess around with - we want to be sure that non of the links get redirected to this workbook that we are messing around with or moved from the original master workbook. EG if the links from our reports get moved to this new workbook we are messing around with it will mean all our figures reports will be wrong.
Therefore can you please explain how these links work. IE, do links between workbooks work by linking to the workbook themselves or to just the name. And what would happen if we make a copy of a workbook with loads of links and then play around wit the copy? what would happen to the links of both the new and existing workbooks?
Thanks in advance for your help!
RE: Excel Links to Other workbooks
Hi Chris,
Thank you for posting your question in our forum. My name is Ron and I am one of STL's trainers.
I can feel your concern. Firstly about links in Excel. When working with a link, one of the files is going to be the source file (your master workbook). The other workbook(s) is the destination file. Links only work from the source file to the destination file. In other words, in the destination file you will have a formula that links to the source file. If you therefore change the source file's information, you would be affecting those destination files and that is what you would like to prevent while working on the master file.
All you have to do to play around with the master file is to copy it and save it in a different location on your machine or network. Because the formulas in the destination files still point to the original file they won't be affected by any changes you make to the copy.
Once the master file is updated with your changes you will need to re-direct the links in the destination file(s), but in principle if no major design changes were done in the copy of the master you can replace the original master file with the modified copyin the same folder and give it the same name as the original file. The links should be picked up automatically by the destination files.
Always work on copies before using it on live data!!!!!!
To answer your last question: Links are based on cells, linked cells have a formula that pulls in the data from the source cell. You could for instance have a value in cell A1 that also appears in cell A1 on sheet 2. You would see a link formula on sheet 2 in Cell A1 that would read: =Sheet1!A1
I hope this makes it clear to you that if you want to work on the master, make a copy and safely change anything without affecting the linked workbooks.
If this is not the answer you wanted or need more clarification please reply to this forum message
Kind regards
Ron Oldeboom
Learning and Development consultant
STL-training
