Thanks for the reply. To clarify, I do want the cell reference to change when I sort. If by sorting the row changes from 4 to 113, I would like the reference to change from A4 to A113. That's mostly what happens. But a few of the rows change the reference to A112 or A114. Or like in Row3 below:

ColA ColB
Row1 Adam =VLOOKUP(A1,...
Row2 Bob =VLOOKUP(A2,...
Row3 Chris =VLOOKUP(A2,...
Row4 Dave =VLOOKUP(A4,...