Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort a column automatically
Is there a way to automatically sort a column in ascending order while
entering numbers so that the column is sorted when last number is entered. Appreciate all help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort a column automatically
Hi!
Why not just sort after the numbers have been entered? Using formulas and a helper column: Assume numbers are entered in column A Enter this formula in column B: =IF(COUNT(A:A)=ROWS($1:1),SMALL(A:A,ROWS($1:1))," ") Copy down. If you expect to enter 10 numbers in column A then copy this formula down 10 rows. Biff "Longtime" wrote in message ... Is there a way to automatically sort a column in ascending order while entering numbers so that the column is sorted when last number is entered. Appreciate all help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort a column automatically
"Longtime" wrote: Is there a way to automatically sort a column in ascending order while entering numbers so that the column is sorted when last number is entered. Appreciate all help. Sorry, that did not do it |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort a column automatically
You might want to try this method using a command button. Substitute what you
need! Private Sub CommandButton1_Click() Me.CommandButton1.Caption = "Sort" 'The CommandButton1 Properties Take FocusOnClick must be set to False Dim rng As Range, rng1 As Range With Worksheets("Sheet1") Set rng = .Range(.Cells(2, "A"), .Cells(Rows.Count, "A").End(xlUp)) Set rng1 = .Cells(2, "A").End(xlToRight) Set rng = rng.Resize(, 3) rng.Sort _ Key1:=.Range("A2"), _ Order1:=xlAscending, _ Header:=xlNo End With Application.OnTime Now + TimeSerial(0, 0, 2), _ ThisWorkbook.Name & "!ResetCaption" CommandButton1.Caption = "Sorting..." End Sub "Longtime" wrote: Is there a way to automatically sort a column in ascending order while entering numbers so that the column is sorted when last number is entered. Appreciate all help. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort a column automatically
Hi Longtime:
Here is a solution that's fun to play with: Say you are entering numbers in an arbitrary order in column A. You can enter them at the top of the column. You can enter them at the bottom of the column. You can leave spaces between entries. Doesn't matter. In B1 enter: =IF(ISERROR(LARGE(A:A,ROW())),"",LARGE(A:A,ROW())) and copy down Whatever you have entered in column A or whatever you will enter in column A will automatically appear in sorted order in column B. Column B will respond to changes in column A in an automatic fashion. It even catches ties. -- Gary's Student "Longtime" wrote: "Longtime" wrote: Is there a way to automatically sort a column in ascending order while entering numbers so that the column is sorted when last number is entered. Appreciate all help. Sorry, that did not do it |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort a column automatically
Sorry, that did not do it
Care to explain what that means? Biff "Longtime" wrote in message ... "Longtime" wrote: Is there a way to automatically sort a column in ascending order while entering numbers so that the column is sorted when last number is entered. Appreciate all help. Sorry, that did not do it |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort a column automatically
sort a column in ascending order
=IF(ISERROR(LARGE(A:A,ROW())),"",LARGE(A:A,ROW()) ) You might want tho replace LARGE with SMALL. Biff "Gary''s Student" wrote in message ... Hi Longtime: Here is a solution that's fun to play with: Say you are entering numbers in an arbitrary order in column A. You can enter them at the top of the column. You can enter them at the bottom of the column. You can leave spaces between entries. Doesn't matter. In B1 enter: =IF(ISERROR(LARGE(A:A,ROW())),"",LARGE(A:A,ROW())) and copy down Whatever you have entered in column A or whatever you will enter in column A will automatically appear in sorted order in column B. Column B will respond to changes in column A in an automatic fashion. It even catches ties. -- Gary's Student "Longtime" wrote: "Longtime" wrote: Is there a way to automatically sort a column in ascending order while entering numbers so that the column is sorted when last number is entered. Appreciate all help. Sorry, that did not do it |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort a column automatically
Why create a command button when you can just use the sort button on the
formatting toolbar? Biff "Richard" wrote in message ... You might want to try this method using a command button. Substitute what you need! Private Sub CommandButton1_Click() Me.CommandButton1.Caption = "Sort" 'The CommandButton1 Properties Take FocusOnClick must be set to False Dim rng As Range, rng1 As Range With Worksheets("Sheet1") Set rng = .Range(.Cells(2, "A"), .Cells(Rows.Count, "A").End(xlUp)) Set rng1 = .Cells(2, "A").End(xlToRight) Set rng = rng.Resize(, 3) rng.Sort _ Key1:=.Range("A2"), _ Order1:=xlAscending, _ Header:=xlNo End With Application.OnTime Now + TimeSerial(0, 0, 2), _ ThisWorkbook.Name & "!ResetCaption" CommandButton1.Caption = "Sorting..." End Sub "Longtime" wrote: Is there a way to automatically sort a column in ascending order while entering numbers so that the column is sorted when last number is entered. Appreciate all help. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort a column automatically
"Biff" wrote: Why create a command button when you can just use the sort button on the formatting toolbar? Biff "Richard" wrote in message ... You might want to try this method using a command button. Substitute what you need! Private Sub CommandButton1_Click() Me.CommandButton1.Caption = "Sort" 'The CommandButton1 Properties Take FocusOnClick must be set to False Dim rng As Range, rng1 As Range With Worksheets("Sheet1") Set rng = .Range(.Cells(2, "A"), .Cells(Rows.Count, "A").End(xlUp)) Set rng1 = .Cells(2, "A").End(xlToRight) Set rng = rng.Resize(, 3) rng.Sort _ Key1:=.Range("A2"), _ Order1:=xlAscending, _ Header:=xlNo End With Application.OnTime Now + TimeSerial(0, 0, 2), _ ThisWorkbook.Name & "!ResetCaption" CommandButton1.Caption = "Sorting..." End Sub "Longtime" wrote: Is there a way to automatically sort a column in ascending order while entering numbers so that the column is sorted when last number is entered. Appreciate all help. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort a column automatically
"Biff" wrote: Why create a command button when you can just use the sort button on the formatting toolbar? Biff "Richard" wrote in message ... You might want to try this method using a command button. Substitute what you need! Private Sub CommandButton1_Click() Me.CommandButton1.Caption = "Sort" 'The CommandButton1 Properties Take FocusOnClick must be set to False Dim rng As Range, rng1 As Range With Worksheets("Sheet1") Set rng = .Range(.Cells(2, "A"), .Cells(Rows.Count, "A").End(xlUp)) Set rng1 = .Cells(2, "A").End(xlToRight) Set rng = rng.Resize(, 3) rng.Sort _ Key1:=.Range("A2"), _ Order1:=xlAscending, _ Header:=xlNo End With Application.OnTime Now + TimeSerial(0, 0, 2), _ ThisWorkbook.Name & "!ResetCaption" CommandButton1.Caption = "Sorting..." End Sub "Longtime" wrote: Is there a way to automatically sort a column in ascending order while entering numbers so that the column is sorted when last number is entered. Appreciate all help. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort a column automatically
"Longtime" wrote: "Biff" wrote: Why create a command button when you can just use the sort button on the formatting toolbar? Biff "Richard" wrote in message ... You might want to try this method using a command button. Substitute what you need! Private Sub CommandButton1_Click() Me.CommandButton1.Caption = "Sort" 'The CommandButton1 Properties Take FocusOnClick must be set to False Dim rng As Range, rng1 As Range With Worksheets("Sheet1") Set rng = .Range(.Cells(2, "A"), .Cells(Rows.Count, "A").End(xlUp)) Set rng1 = .Cells(2, "A").End(xlToRight) Set rng = rng.Resize(, 3) rng.Sort _ Key1:=.Range("A2"), _ Order1:=xlAscending, _ Header:=xlNo End With Application.OnTime Now + TimeSerial(0, 0, 2), _ ThisWorkbook.Name & "!ResetCaption" CommandButton1.Caption = "Sorting..." End Sub "Longtime" wrote: Is there a way to automatically sort a column in ascending order while entering numbers so that the column is sorted when last number is entered. Appreciate all help. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort a column automatically
"Richard" wrote: You might want to try this method using a command button. Substitute what you need! Private Sub CommandButton1_Click() Me.CommandButton1.Caption = "Sort" 'The CommandButton1 Properties Take FocusOnClick must be set to False Dim rng As Range, rng1 As Range With Worksheets("Sheet1") Set rng = .Range(.Cells(2, "A"), .Cells(Rows.Count, "A").End(xlUp)) Set rng1 = .Cells(2, "A").End(xlToRight) Set rng = rng.Resize(, 3) rng.Sort _ Key1:=.Range("A2"), _ Order1:=xlAscending, _ Header:=xlNo End With Application.OnTime Now + TimeSerial(0, 0, 2), _ ThisWorkbook.Name & "!ResetCaption" CommandButton1.Caption = "Sorting..." End Sub "Longtime" wrote: Is there a way to automatically sort a column in ascending order while entering numbers so that the column is sorted when last number is entered. Appreciate all help. The column can always be sorted after the numbers have been entered. I was hoping I could get the numbers sorted without buttons, macros or manually i.e sorted as the numbers are entered. There is also a name attached to the number which should follow. There are no extra columns as it is a form. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort a column automatically
If I enter your code I'll get an error.....?
Rgds // Mats "Gary''s Student" wrote: Hi Longtime: Here is a solution that's fun to play with: Say you are entering numbers in an arbitrary order in column A. You can enter them at the top of the column. You can enter them at the bottom of the column. You can leave spaces between entries. Doesn't matter. In B1 enter: =IF(ISERROR(LARGE(A:A,ROW())),"",LARGE(A:A,ROW())) and copy down Whatever you have entered in column A or whatever you will enter in column A will automatically appear in sorted order in column B. Column B will respond to changes in column A in an automatic fashion. It even catches ties. -- Gary's Student "Longtime" wrote: "Longtime" wrote: Is there a way to automatically sort a column in ascending order while entering numbers so that the column is sorted when last number is entered. Appreciate all help. Sorry, that did not do it |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i multiply two columns | Excel Worksheet Functions | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
How do I sort a column of formulas in Excel? | Excel Discussion (Misc queries) | |||
How do I sort a column of formulas in Excel? | Excel Discussion (Misc queries) |