Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sread sheet that sorts and totals as you enter new lines
I want to develop a spreadsheet ( to use as a cutting list) that will sort
and total entries on a "live" basis, from 1 to 300 lines of entry. Can any one please help, Thanking you in anticipation |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sread sheet that sorts and totals as you enter new lines
Right-click on the sheet tab and paste this macro into the VBE window:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim EndData As Long If Target.Column < 2 Then Exit Sub Application.ScreenUpdating = False EndData = Cells(Rows.Count, 1).End(xlUp).Row With Range(Cells(2, 1), Cells(EndData, 2)) .Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With Application.ScreenUpdating = False End Sub Right now the sheet is set to "Auto Sort" based on values in Column B, starting in row 2. Change the Column to suit your needs. Hope that helps, Ryan--- -- RyGuy "Carpenter Dave" wrote: I want to develop a spreadsheet ( to use as a cutting list) that will sort and total entries on a "live" basis, from 1 to 300 lines of entry. Can any one please help, Thanking you in anticipation |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sread sheet that sorts and totals as you enter new lines
something like this might work;
On Sheet1, use this page for your data entry and use 2nd sheet to display the contents of 1st sheet but hopefully sorted. You should not enter data on Sheet2. on Sheet2, in Col A, insert a column of seqeuntial integers, beginning with 1 and continuing to the maximum number of rows/lines you want sorted. in B1 put the following formula: =INDIRECT(ADDRESS(MATCH(SMALL(Scores!A:A,A1),Score s!A:A,0),1,1,1,"Scores")) you can then select this cell, copy to clipboard, select the number of cells below this equal to the number of filled cells in column A, paste. Cell B2 should then be: =INDIRECT(ADDRESS(MATCH(SMALL(Sheet1!A:A,A2),Sheet 1!A:A,0),1,1,1,"Sheet1")) I use something almost similar to this to sort a subset of my data. however in my case, the leftmost column are dates entered sequentially and i sort to get the 10 best of the 20 most recent. in browsing through help, i ran across 'Filter a range', that might hold promise also. Hope this works and is correct. Carpenter Dave wrote: I want to develop a spreadsheet ( to use as a cutting list) that will sort and total entries on a "live" basis, from 1 to 300 lines of entry. Can any one please help, Thanking you in anticipation |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enter info in one sheet, auto enter in another based on one field | New Users to Excel | |||
Numbers are floating slightly above lines in totals | Excel Discussion (Misc queries) | |||
why don't my totals change when I enter additional data? | Excel Worksheet Functions | |||
Pivot needs BOTH cur/cum totals w/out extra cum cols/repeat lines | Excel Worksheet Functions | |||
numeric sort on one sheet, automatically sorts alphabetical on another? | Excel Discussion (Misc queries) |