Sorting and maintaining formula integrity
 

I have an Excel Sorting question:

I have the following Table:

A B C D
% Rank Cost Markup
13% 5 1.00 =A2
10% 4 2.00 =A3
5% 6 3.00 =A4
8% 2 4.00 =A5
14% 1 5.00 =A6
12% 3 6.00 =A7
etc..

When I select columns B, C & D and sort by "Rank" in Ascending Order, I lose
the integrity of the "Markup" column (column D). The results in columns B &
C get sorted properly Except for column D as follows:

B C D
Rank Cost Markup
1 5.00 13% (=A2)
2 4.00 10% (=A3)
3 6.00 5% (=A4)
4 2.00 8% (=A5)
5 1.00 14% (=A6)
6 3.00 12% (=A7)
etc..

What formula can I enter in column D instead of simply entering =A2, =A3,
etc..?

Thank you.

Ed.

Sorting and maintaining formula integrity
 

Just include col A in your pre-sort selection
Or freeze col D's formulas before you sort
Or put something like this in D2, copied down:
=INDEX({0.13;0.1;0.05;0.08;0.14;0.12},MATCH(B2,{5; 4;6;2;1;3},0))
(above basically hardcodes the pre-sort association between cols A & B -
that's the key)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik
---
"Ed" wrote:
I have an Excel Sorting question:

I have the following Table:

A B C D
% Rank Cost Markup
13% 5 1.00 =A2
10% 4 2.00 =A3
5% 6 3.00 =A4
8% 2 4.00 =A5
14% 1 5.00 =A6
12% 3 6.00 =A7
etc..

When I select columns B, C & D and sort by "Rank" in Ascending Order, I lose
the integrity of the "Markup" column (column D). The results in columns B &
C get sorted properly Except for column D as follows:

B C D
Rank Cost Markup
1 5.00 13% (=A2)
2 4.00 10% (=A3)
3 6.00 5% (=A4)
4 2.00 8% (=A5)
5 1.00 14% (=A6)
6 3.00 12% (=A7)
etc..

What formula can I enter in column D instead of simply entering =A2, =A3,
etc..?

Sorting and maintaining formula integrity
 

Thanks for your reply.

I have so many products (200+) with so many different percentage variables
in column A and it would be difficult to include the percentages in your
formula.

How can I freeze column D's formulas before sorting?

Thanks.

Ed.


"Max" wrote in message
...
Just include col A in your pre-sort selection
Or freeze col D's formulas before you sort
Or put something like this in D2, copied down:
=INDEX({0.13;0.1;0.05;0.08;0.14;0.12},MATCH(B2,{5; 4;6;2;1;3},0))
(above basically hardcodes the pre-sort association between cols A & B -
that's the key)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik
---
"Ed" wrote:
I have an Excel Sorting question:

I have the following Table:

A B C D
% Rank Cost Markup
13% 5 1.00 =A2
10% 4 2.00 =A3
5% 6 3.00 =A4
8% 2 4.00 =A5
14% 1 5.00 =A6
12% 3 6.00 =A7
etc..

When I select columns B, C & D and sort by "Rank" in Ascending Order, I
lose
the integrity of the "Markup" column (column D). The results in columns
B &
C get sorted properly Except for column D as follows:

B C D
Rank Cost Markup
1 5.00 13% (=A2)
2 4.00 10% (=A3)
3 6.00 5% (=A4)
4 2.00 8% (=A5)
5 1.00 14% (=A6)
6 3.00 12% (=A7)
etc..

What formula can I enter in column D instead of simply entering =A2, =A3,
etc..?

Sorting and maintaining formula integrity
 

How can I freeze column D's formulas before sorting?
To freeze, copy col D, then right-click on it Paste special Check
"Values" OK
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800, Files:359, Subscribers:56
xdemechanik
---

Sorting and maintaining formula integrity
 

Is there a formula that can automatically assign the "$" symbol to each cell
in column D to maintain formula integrity as follows:

=$A$2
=$A$3
=$A$4
=$A$5
etc..

Thanks.

Ed

"Max" wrote in message
...
How can I freeze column D's formulas before sorting?
To freeze, copy col D, then right-click on it Paste special Check
"Values" OK
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800, Files:359, Subscribers:56
xdemechanik
---

Sorting and maintaining formula integrity
 

You can use the sub below (from a Gord Dibben's post)
Select the range containing the formulas, eg select D2:D7, then run the sub

Sub Absolute()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1, _
xlAbsolute)
End If
Next
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800, Files:359, Subscribers:56
xdemechanik
---
"Ed" wrote in message
...
Is there a formula that can automatically assign the "$" symbol to each
cell in column D to maintain formula integrity as follows:

=$A$2
=$A$3
=$A$4
=$A$5
etc..