Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell formulas issue in sorting data
Is it possible to keep the cell's orginal formulas after it's been sorted?
E.g. if before sorting D3 = "D1", after sorting, D3 becomes to position of "E4", is it possible to make the "E4" still eaqual to "D1"? It sounds weird, right? Many thanks in advance, please. Grace |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell formulas issue in sorting data
I'm guessing that somehow you wish to retain the associations in say col D
with a key col (eg names or ids) Then one route, instead of using simple links to associate would be to use index/match Try Debra Dalgleish's nice coverage on INDEX/MATCH at her: http://www.contextures.com/xlFunctions03.html INDEX/MATCH There's also some sample workbooks available for d/l & study -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- "Grace" wrote: Is it possible to keep the cell's orginal formulas after it's been sorted? E.g. if before sorting D3 = "D1", after sorting, D3 becomes to position of "E4", is it possible to make the "E4" still equal to "D1"? It sounds weird, right? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell formulas issue in sorting data
Hi, Max,
Thanks a lot for your quick reply, but I think I did not explain my question clearly in the first post. Please let me try again. The following chart is before the sorting: Column A data are all manually key in, Row 1 data are all manually key in; Column B, B2=SUM(C2:E2), B3=SUM(C3:E3), B4=SUM(C4:E4); Column C to E, C2=C1, D3=D1, E4=E1, all the rest of cells are blank. A B C D E 1 Item Sub-total 20 10 30 2 a 20 20 3 b 10 10 4 c 30 30 After sorting the chart by "Sub-total", it becomes as below: A B C D E 1 Item Sub-total 20 10 30 2 b #REF! #REF! 3 a 0 0 4 c 30 30 As you could see all cells between C2:E4, as long as they are moved by sorting, the original formulars were changed...... Would you please let me know if it is possible to fix this issue? Thanks a lot. Grace "Max" wrote: I'm guessing that somehow you wish to retain the associations in say col D with a key col (eg names or ids) Then one route, instead of using simple links to associate would be to use index/match Try Debra Dalgleish's nice coverage on INDEX/MATCH at her: http://www.contextures.com/xlFunctions03.html INDEX/MATCH There's also some sample workbooks available for d/l & study -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- "Grace" wrote: Is it possible to keep the cell's orginal formulas after it's been sorted? E.g. if before sorting D3 = "D1", after sorting, D3 becomes to position of "E4", is it possible to make the "E4" still equal to "D1"? It sounds weird, right? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell formulas issue in sorting data
Replicated your sample table. When I selected the range A2:E4, then did a
Data Sort by "Subtotal" Ascending, the results seem ok, Item Sub-total 20 10 30 b 10 10 a 20 20 c 30 30 How exactly did you do the sorting? The range selection before you sort is all important -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- "Grace" wrote: Hi, Max, Thanks a lot for your quick reply, but I think I did not explain my question clearly in the first post. Please let me try again. The following chart is before the sorting: Column A data are all manually key in, Row 1 data are all manually key in; Column B, B2=SUM(C2:E2), B3=SUM(C3:E3), B4=SUM(C4:E4); Column C to E, C2=C1, D3=D1, E4=E1, all the rest of cells are blank. A B C D E 1 Item Sub-total 20 10 30 2 a 20 20 3 b 10 10 4 c 30 30 After sorting the chart by "Sub-total", it becomes as below: A B C D E 1 Item Sub-total 20 10 30 2 b #REF! #REF! 3 a 0 0 4 c 30 30 As you could see all cells between C2:E4, as long as they are moved by sorting, the original formulars were changed...... Would you please let me know if it is possible to fix this issue? Thanks a lot. Grace |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell formulas issue in sorting data
I did select the range before I do the sorting, and I just tried to the way
you mentioned below, and it still did not work. When you replicated my sample table, did you use the formulas as I said on each "cell" before the sorting? 'Column C to E, C2"=C1", D3"=D1", E4"=E1", all the rest of cells are blank. ' If I don't use formulas in these cells, but only manualy key in the data, it won't have any issues after sorting...... Grace "Max" wrote: Replicated your sample table. When I selected the range A2:E4, then did a Data Sort by "Subtotal" Ascending, the results seem ok, Item Sub-total 20 10 30 b 10 10 a 20 20 c 30 30 How exactly did you do the sorting? The range selection before you sort is all important -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- "Grace" wrote: Hi, Max, Thanks a lot for your quick reply, but I think I did not explain my question clearly in the first post. Please let me try again. The following chart is before the sorting: Column A data are all manually key in, Row 1 data are all manually key in; Column B, B2=SUM(C2:E2), B3=SUM(C3:E3), B4=SUM(C4:E4); Column C to E, C2=C1, D3=D1, E4=E1, all the rest of cells are blank. A B C D E 1 Item Sub-total 20 10 30 2 a 20 20 3 b 10 10 4 c 30 30 After sorting the chart by "Sub-total", it becomes as below: A B C D E 1 Item Sub-total 20 10 30 2 b #REF! #REF! 3 a 0 0 4 c 30 30 As you could see all cells between C2:E4, as long as they are moved by sorting, the original formulars were changed...... Would you please let me know if it is possible to fix this issue? Thanks a lot. Grace |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell formulas issue in sorting data
In C2, instead of: =C2,
use: =INDIRECT("C1") Do similarly for D3 and E4. And that should now survive the sort. -- Max Singapore http://savefile.com/projects/236895 Downloads:20,000 Files:362 Subscribers:62 xdemechanik --- "Grace" wrote: I did select the range before I do the sorting, and I just tried to the way you mentioned below, and it still did not work. When you replicated my sample table, did you use the formulas as I said on each "cell" before the sorting? 'Column C to E, C2"=C1", D3"=D1", E4"=E1", all the rest of cells are blank. ' If I don't use formulas in these cells, but only manualy key in the data, it won't have any issues after sorting...... Grace |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell formulas issue in sorting data
YES!! It works!!
Thank you so much for your help, Max. Have a good day. "Max" wrote: In C2, instead of: =C2, use: =INDIRECT("C1") Do similarly for D3 and E4. And that should now survive the sort. -- Max Singapore http://savefile.com/projects/236895 Downloads:20,000 Files:362 Subscribers:62 xdemechanik --- "Grace" wrote: I did select the range before I do the sorting, and I just tried to the way you mentioned below, and it still did not work. When you replicated my sample table, did you use the formulas as I said on each "cell" before the sorting? 'Column C to E, C2"=C1", D3"=D1", E4"=E1", all the rest of cells are blank. ' If I don't use formulas in these cells, but only manualy key in the data, it won't have any issues after sorting...... Grace |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell formulas issue in sorting data
Welcome, Grace
-- Max Singapore http://savefile.com/projects/236895 Downloads:20,000 Files:362 Subscribers:62 xdemechanik --- "Grace" wrote in message ... YES!! It works!! Thank you so much for your help, Max. Have a good day. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell formulas issue in sorting data
Typo correction in 1st line,
it should read: In C2, instead of: =C1, use: =INDIRECT("C1") -- Max Singapore http://savefile.com/projects/236895 Downloads:20,000, Files:362, Subscribers:62 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems when sorting data containing array formulas | Excel Worksheet Functions | |||
Sorting Worksheet and how it effects Formulas with cell references | Excel Discussion (Misc queries) | |||
Sorting Possible avoiding cell Formulas? | New Users to Excel | |||
Import External Data -- Fill down formulas Issue | Excel Worksheet Functions | |||
Sorting Data that feeds into other formulas.... | Excel Discussion (Misc queries) |