How do I sort data without messing up cel linked equations?

[zxcvbnm6000]

I have a Excel sheet with various data that I want to sort by A-Z, the
problem is I have equations the do not sort correctly.

Example:
Before:
A1 - Mouse B1 - (=A3)
A2 - Dog B2 - ***
A3 - Cat B3 - (=A2)

After A-Z Sort:
A1 - Cat B1 - (#REF)
A2 - Dog B2 - ***
A3 - Mouse B3 - 0

Please help. This should be simple, but I just can't make it work.

 

[Sheeloo]

Change the formulas to
=A$3 and
=A$2

 

[zxcvbnm6000]

That gives me this:
A1 - Cat B1 - Dog
A2 - Dog B2 - ***
A3 - Mouse B3 - Mouse

If it matters, I have Office 2007.

 

[Bob Phillips]

It sorted fine for me, but not as you wanted perhaps. What results are you
looking for?

 

[zxcvbnm6000]

Alright you start with this:
A column is animals. B column is what they fear.
A1[Mouse] B1[=A3](Cat)
A2[Dog] B2[***]
A3[Cat] B3[=A2](Dog)

Should be with A-Z sort:
A1[Cat] B1[Dog]
A2[Dog] B2[***]
A3[Mouse] B3[Cat]

I hope that I am making this clear enough for people, it's not easy with
simple text.

 

[Sheeloo]

Sorry for giving you the WRONG solution...

What you need to accomplish can NOT be done through a simple sort... Excel
does not update formulas to point to the new cell the source cells moves to...

What you need to do is have the same data on another sheet (say Sheet2) and
use this in B1 of original sheet
=VLOOKUP(A1,Sheet2!A:B,2,False)
and copy down till end of you data set

 

[zxcvbnm6000]

This works, but is impractical when dealing with hundreds of rows and
multiple columns of data with equations. Especially when you need to edit
data every once in a while. In my example if you change cat to horse,
following your method I would have to change it in both sheets or receive
errors.

Isn't there a way to lock all the data in one row together or something.

 

[Sheeloo]

If you use VLOOKUP then..

Whatever changes you do in Column 2 of sheet 2 (Lookup sheet) will
automatically show up in Col 2 of Sheet 1
Also if you change any cell in Col 1 of Sheet 1, Col 2 will also
automatically change by looking up the value in Sheet 2.
If you want to change 'Mouse is afraid of Cat' to 'Mouse is afraid of Dog'
then you just need to change Sheet 2 (change value agains Mouse to Dog from
Cat)..

Try it with few samples ... it will give you what you want.

 

[zxcvbnm6000]

I'm sure this will be helpful to me someday, but in the file that I am
currently working with, I am receiving #REF errors everywhere when using
VLOOKUP. It is far to time consuming to edit two sheets of data. It is even
more time consuming to look through math equations to figure out if all the
results are still correct or not.

There has to be something that lock data with cells and allows for easy
sorting. Like in Access (though a completely different program) all data in a
row is locked. You sort the list all the data moves along with its correct
pairing.