I have an inventory workbook with many sheets that are interconnected for my greenhouse operation. On the sheet with actual inventory on it, each line represents a group of plants. Columns indicate such things as the item number, location, quantity, last transplant date etc. A few columns have formulas that refer to the item number in that same line, then look up the expected shipping percentage for that item stored on another sheet.
Example: This sheet is named "Inv"
<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!
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!
