MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Linking 2 Excel files with similar data

Posted by L Wolf on January 19, 2002 6:43 PM

I would like to know if it is possible to somehow connect 2 Excel files saved in the same directory to be linked, so when one is edited - the other one is also updated.

Posted by Robb on January 19, 2002 7:41 PM

You could do it by creating links between cells in the two files. To di this:

- Open both workbooks
- Activate the workbook you want automatically updated ie. the one that will
not be edited by the operator
- Select a cell you want to link
- Type =
- Activate the other workbook
- Select the cell you want to link and hit "enter"
- You will return to the first workbook and the selected cell will
contain the reference e.g. =[FirstBook.xls]Sheet1!$A$11
- Repeat for each cell you want to link
- If you want to, you could include references in formulae, say if you wanted
half the value, you would do as above then add /2 to get
or maybe multiply the vales of two cells e.g.
=[FirstBook.xls]Sheet1!$A$11* =[FirstBook.xls]Sheet1!$B$42

There are a many ways to utilise these references, hope this gives you some ideas.

With this method, if both workbooks are open, the values will update as you go, if not, the
new values will update next time you open the linked workbook.

Any help?



Posted by L Wolf on January 19, 2002 8:05 PM

Thanks Robb
I will try that and I'll let you know if I was successful.