If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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..? |
#3
|
|||
|
|||
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..? |
#4
|
|||
|
|||
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 --- |
#5
|
|||
|
|||
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 --- |
#6
|
|||
|
|||
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 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 --- "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.. |
Thread Tools | |
Display Modes | |
|
|