Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Mar 2009
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello,

    I've got a list like this:

    _____A______B____
    1 | name1 | number
    2 | name2 | number
    3 | name3 | number
    ...

    Column B is either blank, or 0-100

    I'm using autofilter to sort column B descending.

    The data in column A and B is taken from another sheet, I want the list to refresh automatically (as in re-apply the auto filter) whenever the data on the source sheet is changed..

    Pretty sure I've gotta use a macro with "worksheet_change" but I don't know how

    Thanks for your help

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    Filtering and sorting are different operations, so i don't understand what you mean by "I'm using autofilter to sort column B descending". Could you explain?

  3. #3
    New Lounger
    Join Date
    Mar 2009
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='765064' date='12-Mar-2009 12:39']Welcome to the Lounge!

    Filtering and sorting are different operations, so i don't understand what you mean by "I'm using autofilter to sort column B descending". Could you explain?[/quote]

    Well, column B is a list of 'scores' between 0 and 100. I have the AutoFilter set to sort this column from highest to lowest so it's like this:

    Code:
    Bruce Wayne	100
    Clark Kent	 96
    Some Guy	   95
    Some Girl	  85
    The idea is to sort people by top scores.. it works but I have to re-sort by 'descending' every time something changes..

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Which version of Excel do you use?

  5. #5
    New Lounger
    Join Date
    Mar 2009
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='765079' date='12-Mar-2009 13:15']Which version of Excel do you use?[/quote]

    2003

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Although sorting in AutoFilter was added in Excel 2003, VBA support for it was only added in Excel 2007.

    If the column you sort on is fixed, you can use the Sheet_Activate event of the worksheet with the autofiltered table to sort the table each time the user activates the sheet.
    But since you can't inspect the sort aspect of AutoFilter in Excel 2003 VBA, I don't see an easy way to handle it if the user changes the sort column(s).

  7. #7
    New Lounger
    Join Date
    Mar 2009
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I guess that's why this isn't working:

    Code:
    Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("A5:A319")) Is Nothing Then
    
    Run "Top10Update"
    
    End If
    
    End Sub
    Code:
    Sub Top10Update()
    '
    ' Top10Update Macro
    ' Macro recorded 3/12/2009 by Mike
    '
    
    '
    	Range("A1:B316").Sort Key1:=Range("B1"), Order1:=xlDescending, Header:= _
    		xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    		DataOption1:=xlSortNormal
    End Sub
    Driving me crazy.. maybe i'm taking a completely wrong approach here.

    All I really want is a "top 10" list of employees based on test scores.. there's 300+ employees listed in a sheet that's like this:

    Code:
    name	 test1   test2   test3	test4	average
    person	 80	  85	   90	   85		  85
    person2   90	  70	   80	   90		  82.5
    I want to pick out the top ten averages and put them in a little top ten chart on a seperate sheet

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Have you tried using the Worksheet_Activate event instead?

  9. #9
    New Lounger
    Join Date
    Mar 2009
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='765106' date='12-Mar-2009 14:03']Have you tried using the Worksheet_Activate event instead?[/quote]

    You know what, that's a way better idea.. I have it working the way I want! Thanks!

    The only issue now, is this..

    Not all of the employees have data, the average cells are like this:

    =IF(ISERROR(AVERAGEA(A33)),"n/a",AVERAGEA(A33))

    This is to get rid of the #DIV/0! that would normally be there..

    So, there's a bunch of "n/a" in my list.. it looks like this

    Person1 | n/a
    Person2 | n/a
    Person3 | n/a
    Person4 | 100
    Person5 | 90
    Person6 | 80

    Is there a way to make it so the people with actual values are at the top of the list, so it's like this:

    Person4 | 100
    Person5 | 90
    Person6 | 80
    Person1 | n/a
    Person2 | n/a
    Person3 | n/a

    Thanks for your help!

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Excel considers all text to be "higher" than all numbers, so if you sort descending, the text will come first.
    A solution is to add another column with formulas like

    =ISERROR(AVERAGEA(A33))

    and to sort ascending on this column, then descending on your original column. FALSE comes before TRUE, so the numeric averages will be sorted to the top. You can hide the column with the TRUE/FALSE values.

  11. #11
    New Lounger
    Join Date
    Mar 2009
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='765139' date='12-Mar-2009 16:55']Excel considers all text to be "higher" than all numbers, so if you sort descending, the text will come first.
    A solution is to add another column with formulas like

    =ISERROR(AVERAGEA(A33))

    and to sort ascending on this column, then descending on your original column. FALSE comes before TRUE, so the numeric averages will be sorted to the top. You can hide the column with the TRUE/FALSE values.[/quote]


    Thanks for helping.. I couldn't get your idea here to work, I didn't understand it at all actually - my fault, I'm exceltarded.

    I did get what I wanted though, probably in an overcomplicated way.. I used this to make a top ten list based on the full list:

    =INDEX(A2:A400,MATCH(G1,B2:B400,FALSE)) -- in F1
    =INDEX(A2:A400,MATCH(G2,B2:B400,FALSE)) -- in F2 ---- for the names
    =INDEX(A2:A400,MATCH(G3,B2:B400,FALSE)) -- in F3
    etc

    =LARGE(B2:B400,1) -- in G1
    =LARGE(B2:B400,2) -- in G2 --- for the scores [0-100]
    =LARGE(B2:B400,3) -- in G3
    etc

    Now it doesn't matter what damn order the full list is in

    Again, thanks, I couldn't have figured this out without your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •