Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I sort data without messing up cel linked equations?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I sort data without messing up cel linked equations?
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I sort data without messing up cel linked equations?
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I sort data without messing up cel linked equations?
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I sort data without messing up cel linked equations?
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I sort data without messing up cel linked equations?
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I sort data without messing up cel linked equations?
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I sort data without messing up cel linked equations?
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I sort data without messing up cel linked equations?
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. "Sheeloo" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I data sort multiple sheets in Excel that are linked with i | Excel Worksheet Functions | |||
Displaying linked data in excel 2007 when linked file is not avail | Excel Discussion (Misc queries) | |||
Sort without messing up formulas | Excel Discussion (Misc queries) | |||
Sort & add columns without messing up formulas | Excel Worksheet Functions | |||
sort 2 or more rows of cells linked to one row of data | Excel Worksheet Functions |