Sorting a formula column

[imported_unknown]

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.

 

[Legacy 98055]

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]

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]

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

 

[Legacy 98055]

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

 

[Legacy 98055]

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]

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]

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]

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