Referencing a Dynamic Cell

[ztrotman]

Hi, I have a very simple spreadsheet that shows in worksheet 1 a table of scoring, which I may need to sort by score, salary band or both so these cells are dynamic. eg

[TABLE="width: 150"]
<TBODY>[TR]
[TD]Name[/TD]
[TD]Salary Band[/TD]
[TD]Total Score[/TD]
[/TR]
[TR]
[TD]Vic[/TD]
[TD]A[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Jo[/TD]
[TD]B[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]c[/TD]
[TD]16[/TD]
[/TR]
</TBODY>[/TABLE]



In worksheet 2 I have a static table set out horizontally eg
[TABLE="width: 300"]
<TBODY>[TR]
[TD]Salary Band[/TD]
[TD]Name[/TD]
[TD]Total
Score[/TD]
[TD]Salary Band[/TD]
[TD]Name[/TD]
[TD]Total Score[/TD]
[TD]Salary Band[/TD]
[TD]Name[/TD]
[TD]Total Score[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Vic[/TD]
[TD]14[/TD]
[TD]B[/TD]
[TD]Jo[/TD]
[TD]6[/TD]
[TD]C[/TD]
[TD]Bob[/TD]
[TD]16[/TD]
[/TR]
</TBODY>[/TABLE]


What I need to be able to do is in worksheet 2, reference the Total Score cells in worksheet 2 to either the Total Score cells in worksheet 1 wherever they may be (ie they will move if I sort the data in different ways) or to the values of the data in the total score cells.

Is this possible?

 

[cstimart]

Have you tried using VLookup?

 

[Andrew Poulsom]

Welcome to MrExcel.

In C2 on Sheet2:

=INDEX(Sheet1!$C$2:$C$4,MATCH(1,INDEX((Sheet1!$A$2:$A$4=B2)*(Sheet1!$B$2:$B$4=A2),),FALSE))

Copy the formula to F2 and I2.

 

[ztrotman]

Thank you, I'll try this

 

[ztrotman]

Thank you the formula that Mr Excel provided has worked. I couldn't use Vlookup because I can't guarantee that the data will always be assorted in ascending order - but I didn't know if would have to be when I asked the question so thank you for that suggestion as well - I have learnt from it.