Sorting Tables

[DanG]

Hello,

I am new to working with tables and have an issue when sorting. The issue seems to be related to the relative positioning when referencing an adjacent cell. Before sorting, the top cell and all the ones below show the correct relative action ($b2, $b3, $b4...). But after sorting it becomes random ($b2, $b17, $b6...).

I have the formula below for the "Advisor" column and all works perfect before sorting. I am showing the formulas for the 2 top cells so you can see the difference. I have underlined and used bold text to highlight the problem.

Before sorting:

=INDEX(AdvGrpTable[AdvisorName], MATCH(Activity![U][B]$B2[/B][/U],AdvGrpTable[AdvGA'#], 0))

=INDEX(AdvGrpTable[AdvisorName], MATCH(Activity[B][U]!$B3,[/U][/B]AdvGrpTable[AdvGA'#], 0))

After sorting (same top 2 cells):

=INDEX(AdvGrpTable[AdvisorName], MATCH(Activity![B][U]$B3[/U][/B],AdvGrpTable[AdvGA'#], 0))

=INDEX(AdvGrpTable[AdvisorName], MATCH(Activity![B][U]$B2[/U][/B],AdvGrpTable[AdvGA'#], 0))

Yes, you are seeing that correctly. $b cell reference does not change in a relative manner.

I am lost on this one?

Thank you

 

[DanG]

I think I found the solution.

Apparently, tables has an issue with hard references sometimes. So using a dynamic reference seems to be a good workaround, at least it worked for me.

I used this instead:

=INDEX(AdvGrpTable[AdvisorName], MATCH([B][U]INDIRECT("$b"&ROW())[/U][/B],AdvGrpTable[AdvGA'#], 0))