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

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?

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?

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

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

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

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

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.

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
---