Excel cell references not updating when referenced cells are sorted

microsoft excelworksheet-function

There are two tables, each with 75 entries. Each entry in the 2nd table calls an entry in the first table a parent. One of my 2nd table columns contains the "Parent Price", referencing the Price column in the first table, such as "=E50".

Table 1
Id    Price
1001  79.25
1002  8.99
1003  24.50

Table 2
Id    Price    Parent Price
2001  50.00    =B2
2002  2.81     =B3
2003  12.00    =B4

The problem is when I sort the first table, none of the second table's "Parent Price" references are updated, and still point to the =E50 cell, which is no longer the correct parent.

I don't want to have to name the cells if possible.
What style of formula do I enter in the parent price column so that they properly track the cells in the referenced table?

Best Answer

The problem is that you are basiing your reference to the parent purely on position by using a simple formula. What you need to to do is adjust Table 2 so that it uses a pointer to the proper value in Table 1. 

        A       B       C           D
1   Id      Price   Parent      Parent Price
2   2001    50.00   1001        =VLOOKUP(C1, Sheet1!$A$1:$B$30, 2, FALSE)
3   2002    2.81    1002        =VLOOKUP(C2, Sheet1!$A$1:$B$30, 2, FALSE)
4   2003    12.00   1003        =VLOOKUP(C3, Sheet1!$A$1:$B$30, 2, FALSE)

The VLOOKUP function takes three parameters. The first is the value you seek. The second is the range that contains the data such that the first column contains the values which will be used for matching. The last argument is the column in that range that should be return. For example, the first formula is going to Sheet1!$A$1:$B$30 (assuming Table 1 is on Sheet1 in cells A1 to B30) and in the first column looking for the value 1001. When it finds it, it then returns the second column in that range. The $ tells the system to use Absolute references so that when you copy your formulas in Table 2, that range will not change.

In this way, no matter how you re-sort Table 1, the formulas will continue to seek a specific value in that range.

Related Question