Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

1 Answer

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.