Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 16th 08, 12:49 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 20
Default 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   Report Post  
Old September 16th 08, 02:50 AM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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   Report Post  
Old September 16th 08, 03:56 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 20
Default 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   Report Post  
Old September 16th 08, 04:13 AM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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   Report Post  
Old September 16th 08, 04:21 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 20
Default 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   Report Post  
Old September 16th 08, 07:11 AM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting multiple columns and maintaining grouping of information TPSKAM Excel Discussion (Misc queries) 2 December 13th 07 03:31 PM
Maintaining formulas when sorting TMC New Users to Excel 1 January 4th 07 03:08 PM
sorting linked columns across worksheets, maintaing row integrity tam25 Excel Discussion (Misc queries) 0 January 4th 07 12:01 AM
Formula Integrity Not Preserved During Sort in Excel 2000 Kevin Excel Discussion (Misc queries) 1 April 15th 05 10:26 PM
Maintaining cell reference after sorting 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