Sorting a formula column

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424
I have a spreadsheet with 15 columns. Column A is a list of automatic consecutivly fill-in numbers (i.e.) A1+1=A2, etc... I need to sort other columns without changing column A into a "value" rather than a formula. Is there anyway of doing this without loosing either/or the formula or the results of the formula in column A.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
L

Legacy 98055

Guest
Hi AAA
Maybe it's different in earlier versions?
In 2000 the sort will use the values of the formulas without changes being made to the formulas themselves. Am I hearing you on this?

Tom
 

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424
Thanks Tom for answering. I have Excel 2000. When I sort the document, I get the "#Ref" message in the column with formula's. I know if I go to edit/special paste/value, it will change the formulated numbers to actual figures - but I don't want to do that because this is an on-going worksheet that will have many more entries from time to time and if I change the formula to a value, then I have to manually put in the number making human error more likely. This is a quote log, with column A being the assigned quote number. I hope I am making sense to you.
AAA
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
Tom,

Using Excel 97-SR2.

I don't know what the answer is, but consider this:

Starting in A1<pre>
1 B
2 D
3 A
4 G
5 C
6 H
7 J
8 I
9 F
10 E</pre>


Formula in A1 = 1, A2 is =A1+1, copied down to A10.

Sorting on column B produces:<pre>
#REF! A
1 B
2 C
3 D
4 E
5 F
6 G
7 H
8 I
9 J</pre>

B2 = 1, B3 = B2+1. The first cell, A1, always returns #REF.

Regards,

Mike
This message was edited by Ekim on 2002-10-05 21:03
 
L

Legacy 98055

Guest

ADVERTISEMENT

I see.
I missed it altogether. If the formulas refer to a different column then you are fine, but when linked to the same column, that's when you have problems. Same here.
You could add a column temporarily in between
A and B and then sort with values pasted to the new column. I would use a macro for this.

Tom
 
L

Legacy 98055

Guest
Try something like this.
Change A1 and B1 to the first row of data.
Also change the references elsewhere if necc.
<pre>
Sub AutoSort()
Dim LastRow As Long
LastRow = Range("A1:A" & Range("A65536").End(xlUp).Row).Rows.Count
Columns("B:B").Insert Shift:=xlToRight
Range("B1:B" & LastRow).Value = Range("A1:A" & LastRow).Value
Range("B1:P" & LastRow).Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("B:B").Delete Shift:=xlToLeft
End Sub

</pre>

Tom
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

On 2002-10-05 15:32, AAACOMPSVC wrote:
I have a spreadsheet with 15 columns. Column A is a list of automatic consecutivly fill-in numbers (i.e.) A1+1=A2, etc... I need to sort other columns without changing column A into a "value" rather than a formula. Is there anyway of doing this without loosing either/or the formula or the results of the formula in column A.

Hi AAACOMPSVC:

Welcome to the Board!

Sorting a column with formulas leads to unpredictable results.

Unless I am missing something here, the column with formulas is A -- so why don't you do a sort for columns B,C,D,E ... etc. leaving column A out of the sort operation.

Regards!

Yogi
 

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424
Hi Yogi,
I really need column A to correspond with each of the other columns since column A is the quote # assigned to that row. If I don't include it in the sort, I have no way of knowing which quote is associated with the information in a specific row.

I have written a macro and that will probably work; although I'm not sure the people who will be working with this form are "sophisticated" enough to understand the importance of using the macro vs. entering a manual number. Sort of a "user friendly" problem, you know. Thanks for your help though, I appreciate all the input I received.

AAA
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
How about using a series of sequential numbers in column A -- rather than increasing the number in subsequent rows in column A by 1 using a formula. Would that work for you -- or does that raises some other issues in your setup.

Regards!

Yogi
 

Forum statistics

Threads
1,147,451
Messages
5,741,200
Members
423,648
Latest member
steel1968

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top