Re-linking Excel spreadsheets — Business Management Daily: Free Reports on Human Resources, Employment Law, Office Management, Office Communication, Office Technology and Small Business Tax Business Management Daily

Re-linking Excel spreadsheets

by on
in Admin Pro Forum

Question: "Help! I use Word and Excel to prepare documents for the same clients annually.  For 2006, I will use the 2005 Word file and save it (using Save As) into a new 2006 file.

"I have many Excel tables linked to my Word documents, so I usually find the first Excel table in the document, double-click on it to open the Excel document, and save the Excel document into the new 2006 file (using Save As). Then, I update that first table, which is now in the 2006 file.

"My problem is that there are many more Excel tables in my document and all the other tables are still linked to the 2005 Excel document. I usually just delete each table (linked to the 2005 file) as I come to it and re-copy & paste (with link) the same table, from the 2006 file.  I know you can break the links or change the source of the links, but I have been able to break only one table's link at a time, which takes as long as cutting, recopying & repasting.

"Is there any way of re-linking the entire Excel document to the new 2006 file? The file is exactly the same as the previous year, except that it now says '2006' instead of '2005,' so each worksheet is the same and has the same name.  Can anyone out there help?"  -- Diana


Comments

Hi Diana: Have you tried, selecting the repetitive data, and doing a paste special, then just paste formats? I work on excel the entire day and I do lots of plots and graphs and I use "paste special" quite a bit rather than just "paste", because it is quite selective in pasting only what you want pasted. But do make sure you select the exact data area or number of cells you want to paste from and to between documents. Maybe you can experiment by creating a test file first.

I hope this helps.

Millie Moscoso

You can take out all the 2005 figures and leave blank and then save it as a template.

Then each year you can use the template with blank fields and add new figures each year. That way you know you changed each figure since they were blank.

I just went to an Excel workshop last week if you have any questions about the template.

Kenda

This may or may not help. I have several payroll files each year that link to each other in Excel. I name the files "Current" and have links to other "current" files. Then after I close my accounting year, I copy and paste values and rename the file giving it a name with the year (Payroll 2005, Labor 2005, Projects 2005, etc.) This way I still have all the information for that year, but no links are changing the figures. Then I clear out the "Current" numbers and start over.

I truly appreciate everyone's attempt to help, but those solutions would not work in this situation. The links I'm referring to are between Word and Excel documents, not the links inside cells or between Excel documents. Those links are not a problem. However, since I posted this problem, I've played with the documents and, although I cannot relink an entire Excel workbook to my Word document with one click, I've discovered how to highlight all the table links and individually relink each table to the updated document. I can easily have thirty or more tables linked to my Word document, so there is a lot of mouse-clicking to get linked to the correct file, but this is still the best solution I've found. Thanks so much for trying, and perhaps others can benefit from the information you provided.

Leave a Comment