Sorting and maintaining formula integrity

[Ed]

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.

 

[Max]

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]

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]

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

 

[Ed]

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]

You can use the sub below (from a Gord Dibben's post)
Select the range containing the formulas, eg select D27, 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