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:
Befo
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.

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



"zxcvbnm6000" wrote:

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:
Befo
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.

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

If it matters, I have Office 2007.


"Sheeloo" wrote:

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



"zxcvbnm6000" wrote:

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:
Befo
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.

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" wrote:

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

If it matters, I have Office 2007.


"Sheeloo" wrote:

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



"zxcvbnm6000" wrote:

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:
Befo
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.

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" wrote:

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

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" wrote:

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" wrote:

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

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

--
__________________________________
HTH

Bob

"zxcvbnm6000" wrote in message
...
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:
Befo
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.

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.

"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob