Excel Tip: Easily update Linked Spreadsheets
Kathy, of Phoenix writes, "I have multiple Excel spreadsheets that link to each other to form monthly reports. However, each year I spend hours and even days updating all of the links to match the current year spreadsheets. Is there an easier way to do this?"
The age-old business problem with linked spreadsheets is that keeping data for each year can make very large spreadsheets. Therefore, most users I've worked with have created spreadsheets for each year.
This is a smart option if you don't have access to databases meant to handle large amounts of multiple years-worth of data.
This tip is so easy it's scary.
First, all spreadsheets containing links should be setup within one main folder named something including the year.
For example: I've added a folder on my O:\ drive called "2010" and placed all of the Excel spreadsheets within that folder.
HINT: The main folder may contain sub-folders, if you prefer to organize the files this way.
Once the main folder of "2010" is setup and all sub-folders and linked spreadsheets are saved under this folder, the change to the next year becomes a very quick and easy step.
Simply "Copy" the "2010" folder and all of its contents. Rename the "2010 (Copy)" to "2011" and all links will automatically be updated for you.
Each year, continue updating the main folder with the current year by copying the folder and all of its contents. I bet you didn't realize it was that easy, did you?