I get the wobbles when I sort...

[d'Az]

Hi List,
I have an Excel file with data listed alphabetically.
Simplified, it is:
ant bothers bee
bee bothers ant
cat chases dog
dog chases elk
elk chases gnu
fly annoys fly
gnu chases hog
hog chases cat
With C1 data entered manually using the formula =A2,
C2 ditto =A1,

Since it's easier to work in logical groups, col.2 is a sort code. However, sorting according to col.2 affects the formulas, the above becoming:
fly annoys fly
ant bothers bee
bee bothers ant
cat chases dog
dog chases elk
elk chases hog
gnu chases hog
hog chases bee
What can I do to fix the relationship between the two cell contents rather than their cell refs.?
Any help much appreciated.

[AlfD]

Hi!

Well, you could "fix" the values by copying the table, then pasting it back on top of itself using paste special > values.

But, since this destroys the formulae, you might want to keep a formula version somewhere safe.

Alf