My problem is this:
In Sheet2, I want to reference an item (let's call it Widget 1) in Sheet1. So, in a cell in Sheet2, I have:
This means that the cell in Sheet2 will always equal the item that is in B65, even if I insert a row and Widget 1 ends up in B66. The cell in Sheet 2 will now be:
This is all well and good, but if I sort by any other column in Sheet1, then, instead of changing B65 to B<whatever row widget 1 is now in>, the cell in Sheet2 stays B65.
Shouldn't the sort affect the changing reference to the cell the same way that inserting rows does? I always want the cell in Sheet2 to reference Widget 1, wherever it moves to by insertion AND/OR sort.