How to keep referencing same cell after sorting.

[matrex]

Hi everyone,

Say cell A1 has 55
Cell B1 has the formula =A1

Sort only column A (the 55 now becomes in a different row)
Cell B1 chnages value because it is referencing cell A1 which has now a new value.

I want cell B1 to ALWAYS get the value from that orginal cell (i.e. 55) no mmatter where that cell gets located after sorting.

INDIRECT, and Nammed Ranges don't seem to do it.

Thanks.

[NBVC]

In another sheet, enter in column A the numbers 1 to whatever your last filled row number in the first sheet is.

so have a list starting in A1 like:

1
2
3
4
5
... etc


then back in your original sheet enter formula in B1:

=INDEX(A:A,Sheet3!A1) where sheet3!A1 lists the number 1

and copy down

Now sort by column A

[matrex]

No joy! Cell B1 strill makes reference to A1 and not to the value that used to be in A1.

Any other ideas?

[NBVC]

You might need VBA for this... can't think of anything right now.

[matrex]

I am sure there are other ways to do it. BTW I was able to achieve this using cell Comments which hold the cell references and whuch can be read using a user defined function, then with a vlookup formula the value of the original cell can be pulled no matter where that cell is. It works fine but it is not a practical solution for large data lists.

Anybody else whith some ideas?

[martindwilson]

a messy way is to insert another new column A then number down as far as you want
in c1 then put =LOOKUP(1,A:A,B:B)
whenever you sort the old column a(now col b )sort by both col a&b it will always return where the position of 1 i.e the same number

[matrex]

Nope!
Returns #N/A after sorting.

[martindwilson]

use
=VLOOKUP(1,A:B,2,FALSE)
instead

[matrex]

Brilliant!

That does it. Thank you very much Martin

[moin]

Hi, i am also looking for same solution, but i can not make the =VLOOKUP(1,A:B,2,FALSE) right. Can anyone please help?

[Pepe Le Mokko]

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread