Cell referencing broken on column sorting

[dominatro]

I have a long list of names and numbers that are also associated with some data, some of the data cells reference other data cells in the same column. When i apply filters to the name or number columns the cells that have references are broken.

I've attached an example of this with a bit more explanation inside.

How do I keep the references form breaking?

Thanks for your help.

[FDibbins]

references always change when you sort. If you want then to always reference C2, then use =indirect("c2")

[dominatro]

i dont want the reference to stay c2 i want it to stay "linked" to Michael's data cell. So if Michael's data is moved to c15, brad's data should now reference c15. excel's calculation just adjust the formula to continue referencing a cell that is the same number of cells as it had originally, ie: Brad's data cell, C4 was equal to C2. After sorting Brad's data cell is C1, and it throws a reference error because it is trying to reference C-1, when really Michael's data cell is now C5 and thus Brad's should be =C5.

[Jonmo1]

If you include the SheetName in the reference formula, it will 'stick' to that cell when sorted.
i.e. Insted of =C5, try =Sheet1!C5

[dominatro]

That won't work either. I need the reference to update to the new location of the data it is referencing. See my example and sort by number, you will notice that the reference breaks.
The names are ordered alphabetically when you first open the spreadsheet, brad's data references Michael's data cell; C2=C5. If you sort by number from smallest to largest, Brad's data cell now references C9 when i need it to reference C2 because Michael's data cell is now C2.

[snick45]

Just use Vlookup? =VLOOKUP("Michael",$B:$C,2,FALSE)

[dominatro]

Just use Vlookup? =VLOOKUP("Michael",$B:$C,2,FALSE)

That's works perfectly! Thank you!