Reference cells to cells in other spreadsheets, even when sorting

[wayneeustace]

Hi

I have the need to reference cells in 3 different spreadsheets but they change every time I sort the data.

It's a little complex to explain - is it possible to upload samples ?

Thank you in advance

Wayne

 

[Special-K99]

Not saying I'll answer this but FYI

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[wayneeustace]

Thanks - I'll try using Tables and see how we go:

Book 1
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Larry[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]=(B1/C1)[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Moe[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]=(B2/C2)[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Curly[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]=(B3/C3)[/TD]
[/TR]
</tbody>[/TABLE]

Book 2
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Larry[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]=(B1/C1)[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Moe[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]=(B2/C2)[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Curly[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]=(B3/C3)[/TD]
[/TR]
</tbody>[/TABLE]

Book 3
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Larry[/TD]
[TD="align: center"]=average(C1:D1)[/TD]
[TD="align: center"]=[Book1.xlsx]Sheet1$D$1[/TD]
[TD="align: center"]

=[Book2.xlsx]Sheet1$D$1​

<strike></strike>[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Moe[/TD]
[TD="align: center"]=average(C2:D2)[/TD]
[TD="align: center"]

=[Book1.xlsx]Sheet1$D$2​

[/TD]
[TD="align: center"]<strike></strike>

=[Book2.xlsx]Sheet1$D$2​

[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Curly[/TD]
[TD="align: center"]=average(C3:D3)[/TD]
[TD="align: center"]

=[Book1.xlsx]Sheet1$D$3​

[/TD]
[TD]<strike></strike>

=[Book2.xlsx]Sheet1$D$3​

[/TD]
[/TR]
</tbody>[/TABLE]

Thanks if you've got this far The trouble I have is that in Books 1 and 2, the values in columns B and C will change, thus making column D change. When I re-sort those lists in Books 1 and 2, the references in Book 3 no longer refer to the same cells, which therefore makes my Book 3 data inaccurate.

Any assistance would be greatly appreciated.

 

[wayneeustace]

Anyone got any ideas for this one ?

 

[Special-K99]

If column A data is unique can't you just do an INDEX(...MATCH(...)) for in Book 3 for columns C and D ?