# Sorting moves my references

#### sriche01

##### Board Regular
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"
 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!

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
you could run a macro that puts the correct formulas in the top row and copies them down - just click it whether a sort has occurred or not

Remove the inv references:

=sumif('Item Overview'!\$G\$4:\$G\$831,\$a1,'Item Overview'!\$Q\$4:\$Q\$831

rather than:
=sumif('Item Overview'!\$G\$4:\$G\$831,inv!\$a1,'Item Overview'!\$Q\$4:\$Q\$831

and the sorting will be fine.

Thank you both. Rory, your fix is both simple and effective. Many thanks!

I hope it's ok to jump on this thread.

This was a great little fix. I was wondering if anyone knows why Excel adds the local worksheet to some formulas even if we are referencing the same tab? I usually write the formula pointing to "A3" but later when I return, the formula becomes Tab!A3

If you click another sheet (eg to refer to a range on it) and then click back to the formula sheet, any cells you select on the formula sheet are automatically prefixed with the sheet name. It's annoying!

Replies
11
Views
673
Replies
11
Views
612
Replies
3
Views
770
Replies
1
Views
292
Replies
1
Views
184

1,219,672
Messages
6,149,618
Members
450,904
Latest member
Gracifer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back