Realative cells

[Devilsbane]

One thing that is great about excel is that when you cut a cell with an equation in it, the cell numbers can be modified to be pasted anywhere.

But what if I don't want this to happen? Let me explain.

Column A is a name. Column B is a score. Column C is the points behind. Column C is calculated by the top score(which should be located in B2) minus the score found in the cell on that row.

However when I resort this row, I find that a lot of the points behind cells error out because when the entry moves up the B2 in that equation becomes B1 and then goes off the page.

So can I fix the equations in Column C so that no matter what they don't change equations.

Thanks

 

[DonkeyOte]

Not entirely clear - perhaps you mean something along the lines of:

C2:
=INDEX($B:$B,2)-$B2
copied down to remaining rows

 

[Devilsbane]

Not entirely clear - perhaps you mean something along the lines of:

C2:
=INDEX($B:$B,2)-$B2
copied down to remaining rows

I'll give you the credit for solving this one. I used your index function along with ROW() for the index. I know that the equations still move, but they reference their current row and it works wonderfully.

Thanks!