Sread sheet that sorts and totals as you enter new lines

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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---
 
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),Scores!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),Sheet1!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.
 
Back
Top