How do I stop a table sort in one sheet from changing the variable of a cell in another sheet that references the cell in that sorted table?

Question

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:

=Sheet1!B65

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:

=Sheet1!B66

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.

Answer 1

The reference to a cell does not relate to its contents, but only to a cell address.

To do what you want to achieve, you may want to create a helper column and indicate the desired value in the helper column.

In Sheet2 you can then use a lookup construct with Index/Match like

=INDEX(Sheet1!B:B,MATCH("select me",Sheet1!D:D,0))

Now you can sort the source table by a different column, and the lookup formula will still return the correct value. You can hide the helper column to keep your workbook tidy.