Filter Sorting Changes Values of reference cells due to new order

Question

Hello Good people,

I am trying to sort data that has references in it to data in that same column. The numbers keep changing though and I want them to stay the same. I could copy and paste and values but then I wont be able to update the data later.

This is an example of what I want: 

Original	ColumnA	ColumnB	What I want	ColumnA	ColumnB Sort
Row1	=1	=R	Row1	=3	=R
Row2	=A3{the 3}+1 (=4)	=S	Row2	=1	=S
Row3	=3	=Q	Row3	=A1{the 3}+1 (=4)	=Q

However, This is what happens with a normal sort:

Original	ColumnA	ColumnB	Sort by B	ColumnA	ColumnB Sort
Row1	=1	=R	Row1	=3	=S
Row2	=A3+1 (=4)	=S	Row2	=1	=R
Row3	=3	=Q	Row3	=A4+1 (=1)	=Q

I tried locking references (Absolute Reference), but that failed:

Original	ColumnA	ColumnB	Sort	ColumnA	ColumnB Sort
Row1	=1	=R	Row1	=3	=Q
Row2	=$A$3+1 (=4)	=S	Row2	=1	=R
Row3	=3	=Q	Row3	=$A$3+1 (=ERROR)	=S

Is there I formula that I can use to do this? Or is this not a thing? Or does this have a name that I'm not aware of?

Also I can show screenshots of the worksheet too.

Thank you - Kelsey

Answer 1

Hi,

I suggest you to custom sort the column “C” to get the below result in the excel sheet:

Original        Col A    Col B                  

Row 3   3         Q      Row 3    4      

Row 1   1         R       Row 1    3      

Row 2   4         S       Row 2    1      

• For the value to remain unchanged in cell A2 we can use absolute reference in the formula, when we try to do that are you getting circular reference error?
• Do you see any value in the cell with respect to which the circular reference error showed?

For reference you can refer to the article Remove or allow a circular reference:

https://support.office.com/en-us/article/Remove-or-allow-a-circular-reference-0b969f34-d1d9-427a-926b-6e1b64e1ac0b

Hope it helps.