Microsoft Excel - What Happens To Cell References When You Sort

http://stackoverflow.com/questions/7979923/how-do-i-sort-a-table-in-excel-if-it-has-cell-references-in-it

The link has some nice suggestions. 

A problem occurs when you have some data in a sheet that are referred in, say, another sheet 9say, sheet2) and when you sort the data in sheet 1 of a sheet. The cell references do not change when you sort data and hence the data in the other sheet now point to different cells.

I have given a sample example. Click the google spreadsheet. Column A in Sheet2 is set to the values in column A of sheet 1 through formula. Columns A and B in each sheet are pasted again in columns D and E for visual reference only.

N sort the data in cols A and B by selecting these two columns, sort on column A. Go to sheet 2 and see where the values for the fruits were same as before.. Compare with columns D and E in sheet 2.. See?

But if cells or rows or columns were cut and pasted somewhere else (in sheet1), the referring cells (shee2 in this case) retain their integrity. If you sort the values, they don't. 

This isn't intuitive but that's the way it works.

Similarly, let's say we use vlookup (or hlookup) on a set of columns (or rows). Now if we insert a new column (or row) within that set, the formula is likely to go wrong depending on where you inserted the new row (or column). Again not intuitive.