Example: This sheet is named "Inv"

Item Number | Shipping % | Formula for b column |

BUDD BLK18 | 90% | =sumif('Item Overview'!$G$4:$G$831,inv!$a1,'Item Overview'!$Q$4:$Q$831 |

BUXU GNG18 | 92% | =sumif('Item Overview'!$G$4:$G$831,inv!$a2,'Item Overview'!$Q$4:$Q$831 |

CHAM GOM10 | 85% | =sumif('Item Overview'!$G$4:$G$831,inv!$a3,'Item Overview'!$Q$4:$Q$831 |

<tbody>

</tbody>

In the example, Item Overview is a static list with the item number as primary key (no duplicates) with information about each item (column g) including shipping % (column q).

The problem is that my inventory sheet (the above table) is sorted frequently, sometimes by location, sometimes by item number, and occasionally by other columns. Every time that it is sorted, the reference in the above formula to the item number on the same row moves to reference a different row. So, in the above example, I would get the wrong shipping % to show up for each item because it is no longer referring to the item number in the same row as the formula.

So far, any time I sort, I correct the reference in the top line and copy down the 1500 or so rows, but I was wondering if there is a trick to get the reference to move correctly with the sort. Thanks!