cell formulas issue in sorting data

[Grace]

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

 

[Max]

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]

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]

Replicated your sample table. When I selected the range A2:E4, then did a
Data > Sort by "Subtotal" > Ascending, the results seem ok, re:

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]

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]

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]

YES!! It works!!
Thank you so much for your help, Max.
Have a good day.

 

[Max]

Welcome, Grace
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000 Files:362 Subscribers:62
xdemechanik