ExcelBanter - Linking Cells from seperate spreadsheets

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Linking Cells from seperate spreadsheets (https://www.excelbanter.com/excel-worksheet-functions/71669-linking-cells-seperate-spreadsheets.html)

[email protected]

Linking Cells from seperate spreadsheets

I have two spreadsheets and both contain stock levels. For efficiency
and simplicity I would like to be able to link two cells that are in
different spreadsheets, so that no matter which file was updated the
other file would automatically update in tandem

Can anybody help me? Is this possible?

Regards

Conor

[email protected]

Linking Cells from seperate spreadsheets

Note that the spreadsheets are saved as different .xls files

George

Linking Cells from seperate spreadsheets

wrote:
I have two spreadsheets and both contain stock levels. For efficiency
and simplicity I would like to be able to link two cells that are in
different spreadsheets, so that no matter which file was updated the
other file would automatically update in tandem

Can anybody help me? Is this possible?

Regards

Conor

Excel can link directly to a cell
Eg. =[FilenameHere]Sheet1!$A$1
It's easier just to copy the cell then use paste special paste link in
the spreadsheet you wish to use it in. That will give it the correct
filename sheet etc.

Linking back poses a problem you basically must set up one as the master
and the other as a slave (which always copies the data). Otherwise you
must have some way of determining which sheet has the most current data.

Here is 1 possible solution but it is messy, and I would recommend you
work with the master / slave concept.

Eg.
A1 = "The data you want to use"
A2 = Date / Time stamp of when data was entered
A3 = This is the cell you would use in your calculation
It is either the DATA from this sheet or the data from the other
sheet based on which Date/Time is newest

So Book1 cell A3
=IF([Book2]Sheet1!$A$2 A2, [Book2]Sheet1!$A$1, A1)

And Book2 cell A3
=IF([Book1]Sheet1!$A$2 A2, [Book1]Sheet1!$A$1, A1)

Then you can edit/update cell A1 in either sheet, so long as you update
the Date/Time stamp also

George

[email protected]

Linking Cells from seperate spreadsheets

Thanks George. Thats been a great help!

Regards

Conor

All times are GMT +1. The time now is 06:41 AM.