Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
    
is the widget static or does it change value? – Raystafarian Jul 16 '13 at 9:50

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.

enter image description here

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

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

enter image description here

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.

enter image description here

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .