Remember Me? September 16th 08, 12:49 AM posted to microsoft.public.excel.worksheet.functions
 Ed[_4_] external usenet poster First recorded activity by ExcelBanter: Aug 2008 Posts: 20 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. September 16th 08, 02:50 AM posted to microsoft.public.excel.worksheet.functions
 Max external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,221 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..? September 16th 08, 03:56 AM posted to microsoft.public.excel.worksheet.functions
 Ed[_4_] external usenet poster First recorded activity by ExcelBanter: Aug 2008 Posts: 20 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..? September 16th 08, 04:13 AM posted to microsoft.public.excel.worksheet.functions
 Max external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,221 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
--- September 16th 08, 04:21 AM posted to microsoft.public.excel.worksheet.functions
 Ed[_4_] external usenet poster First recorded activity by ExcelBanter: Aug 2008 Posts: 20 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
--- September 16th 08, 07:11 AM posted to microsoft.public.excel.worksheet.functions
 Max external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,221 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 7, 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 Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post TPSKAM Excel Discussion (Misc queries) 2 December 13th 07 03:31 PM TMC New Users to Excel 1 January 4th 07 03:08 PM tam25 Excel Discussion (Misc queries) 0 January 4th 07 12:01 AM Kevin Excel Discussion (Misc queries) 1 April 15th 05 10:26 PM GRITS Excel Discussion (Misc queries) 1 December 2nd 04 08:30 PM

All times are GMT +1. The time now is 09:48 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd. Copyright ©2004-2021 ExcelBanter.
The comments are property of their posters.

# About Us

"It's about Microsoft Excel"

Copyright © 2017