Home 
Search 
Today's Posts 
#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 presort 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 presort 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 presort 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 presort 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 rightclick 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 rightclick 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.. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Sorting multiple columns and maintaining grouping of information  Excel Discussion (Misc queries)  
Maintaining formulas when sorting  New Users to Excel  
sorting linked columns across worksheets, maintaing row integrity  Excel Discussion (Misc queries)  
Formula Integrity Not Preserved During Sort in Excel 2000  Excel Discussion (Misc queries)  
Maintaining cell reference after sorting  Excel Discussion (Misc queries) 