Referencing Cell Values in Another Worksheet

[jimp823]

Hi,

I have data spread across several worksheets. When I change the data in the Master spreadsheet I would like it to update data in the other worksheets. For example, when I update a sale in the Master spreadsheet from Pending to Sold, I would like to also update in the relative sheets.

When I try to create a link from the Master spreadsheet to one of the related spreadsheets by pasting a hyperlink (for example, the formula for the cell in the related spreadsheet would be "='[Master Spreadsheet]Sheet A'!$E$10"), the cell references the cell location in the Master spreadsheet at E10.

However, I'm finding that in the relative sheets, the cells reference the specific cell location in the Master sheet (E10), rather than being tied to the data the in the Master sheet. Therefore, if I sort column E in the Master spreadsheet, the value in E10 may have moved to E40, but the relative spreadsheet is now showing the new data that has move into E10, while I would like it to still show the value that has moved to E40.

How would I accomplish this?

 

[sergioMabres]

Hi Jim
Here is an idea: You need an unique id for the row, so instead of ='[Master Spreadsheet]Sheet A'!$E$10")
You would write =vlookup(a2,'[Master Spreadsheet]Sheet A'!$A:$E,5,0)
Where column A has the unique row id in both sheets
If you change or sort rows in master sheet or slave sheet the results will always be the same
I hope this helps
Cheers
Sergio

 

[jimp823]

Hi Sergio,

Thanks for the help! I'm not familiar with unique row IDs. Is it just a matter of creating a new column in both sheets and creating a unique corresponding value for all the rows?

I found this article about creating reference IDs. Should I follow the steps here? Creating unique reference IDs in Excel | Geek Rant dot org

Thanks again!
Jim

 

[jimp823]

Hi Sergio,

Never mind I figure it out! Your solution worked. Thanks very much!

Jim

 

[sergioMabres]

Glad to help!