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

	A	B	C	D
1	Larry	17	6	=(B1/C1)
2	Moe	16	4	=(B2/C2)
3	Curly	15	3	=(B3/C3)

<tbody>
</tbody>

Book 2

	A	B	C	D
1	Larry	11	6	=(B1/C1)
2	Moe	10	4	=(B2/C2)
3	Curly	14	3	=(B3/C3)

<tbody>
</tbody>

Book 3

	A	B	C	D
1	Larry	=average(C1:D1)	=[Book1.xlsx]Sheet1$D$1	=[Book2.xlsx]Sheet1$D$1​ <strike></strike>
2	Moe	=average(C2:D2)	=[Book1.xlsx]Sheet1$D$2​	<strike></strike> =[Book2.xlsx]Sheet1$D$2​
3	Curly	=average(C3:D3)	=[Book1.xlsx]Sheet1$D$3​	<strike></strike> =[Book2.xlsx]Sheet1$D$3​

<tbody>
</tbody>

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 ?