When I reference a cell in a separate list, I am unable to sort that list and keep the reference. Any suggested solutions?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike C - 15 Feb 2008 06:42 GMT

I have noticed that when I reference a cell (eg, in worksheet 1) from
a list (eg, in Worksheet 2) in another spreadsheet, and then sort the
list (in Worksheet 2), that I lose me reference (in worksheet 1). Does
anyone know how I can alter the reference, or user settings so that I
can avoid this problem?

Thanks for any suggestions!

Reply to this Message

Roger Govier - 15 Feb 2008 12:52 GMT

Hi Mike

Instead of pointing to the cell in the second sheet, can you make the entry
a Vlookup of some value from the other sheet.
Then it will find the correct value, even after sorting.
For example, if column A of Sheet1 had Names, and column B was using your
existing reference to find a value belonging to that Name from Sheet2, you
could use
=VLOOKUP(A2,Sheet2!$A:$B,2,0)

Signature

Regards
Roger Govier

> I have noticed that when I reference a cell (eg, in worksheet 1) from
> a list (eg, in Worksheet 2) in another spreadsheet, and then sort the
[quoted text clipped - 3 lines]
>
> Thanks for any suggestions!

Reply to this Message

Dave K - 15 Feb 2008 14:32 GMT

On Feb 15, 6:52 am, "Roger Govier"
<roger@technology4unospamdotcodotuk> wrote:

> Hi Mike
>
[quoted text clipped - 19 lines]
>
> - Show quoted text -

Ok, thanks Roger. I should have thought of that. I will give it a
shot.

Reply to this Message