Linking two Office documents

Question

Problem:

I have two spreadsheets that each serve different purposes but contain one particular piece of data that needs to be the same in both spreadsheets. This piece of data (one of the columns) gets updated in spreadsheet A but needs to also be updated in spreadsheet B.

Goal:

A solution that would somehow link these two spreadsheets together (keep in mind that they exist on two separate LAN shares on the network) so that when A is updated, B is automatically updated for the corresponding record.

*Note that I understand fully that a database would probably be a better plan for tasks such as these but unfortunately I have no say in that matter.

**Note also that this needs to work for Office 2003 and Office 2007

Answer 1

So you mean that AD743 on spreadsheet B must be equal to AD743 on spreadsheet A? Try this:

• Open both spreadsheets on the same machine.
• Go to AD743 on spreadsheet B.
• Type =.
• Go to spreadsheed A and click on AD743.
• Press enter.

You'll notice that the formula is something like '[path-to-file+file-name].worksheet-name!AD743'.

The value on spreadsheet B will be updated when you open it. In fact, it will ask you if you want to update. Of course, your connection must be up and running for it to update. Also, you can't change the name or the path of spreadsheet A.

Answer 2

I can't say if this is overkill without knowing the details of your usage case, but consider creating a spreadsheet C to hold all data held in common between the two. Links can become dizzyingly complex as spreadsheets age, and having a shared data source might help clear up the confusion.

Perhaps even more "enterprise-y" is the concept of just pasting in all data that otherwise would be shared. That is the official best practice in my company, because external links have caused so much trouble with maintainability. It may seem cumbersome at first, but I've found it may just be the best way to promote maintainability in addition to ease of use, assuming you don't mind the manual intervention.