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?

  • 1
    What is the criteria for matching the parent price in table 2 to table 1? Seems like a formula that would look for a match and use that to reference would be more stable. Can you post the details on what thought process is used to match the parent price in table 2? – guitarthrower Apr 22 '10 at 15:22

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.

  • VLOOKUP fails when the referenced range is not sorted by Id. If the Id order is 1002, 1001, 1003, ..., the VLOOKUP for 1001 returns #N/A, since the search gave up when it found 1002. Since Table1 will be sorted many different ways, the Id column will almost never be numerically sorted, and it is the only appropriate unique key. Otherwise, solution would be good. I need a solution that works with any sorting of the parent table. – Robert Kerr Apr 22 '10 at 16:30
  • Is there another type of cell reference notation that follows the referenced cell? I guess this is because the cell isn't really moving, only the contents are. I'll need to rethink the design. – Robert Kerr Apr 22 '10 at 16:45
  • 1
    His formula's should look like this: =VLOOKUP(C1, Sheet1!$A$1:$B$30, 2, 0) The extra argument looks for an exact match and the data does not need to be sorted in any particular way. – Sux2Lose Apr 22 '10 at 16:57
  • Win. Thomas if you'll edit the post I'll accept it. Thanks to both of you. – Robert Kerr Apr 22 '10 at 17:11
  • @Robert Kerr - Yes. I forgot the last parameter which requires that VLOOKUP do an exact match. I've updated the response to match. – Thomas Apr 23 '10 at 20:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.