Results 1 to 2 of 2

Thread: Automatically sort a list when the numbers are changed.

  1. #1

    Automatically sort a list when the numbers are changed.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a table with a list of numbers on the left column and text on the right column. The list of numbers are referencing other cells to get their values. The spreadsheet is a skill inventory for where I work, and as you fill it out, it will adjust a score for different categories (like communicator, leader, technical etc.) The cells in the list, are referencing the cells that have the scores for each category. The intent of the list is to automatically sort in real-time the categories you have the highest score in. I then have 2 formulas (LARGE functions) that pull the highest and second highest number from the list. Then I use 2 VLOOKUP functions that use the numbers I pulled out from the LARGE functions in order to go through the list and pull out the text of the corresponding highest and second highest number in the right column. I have code that I will paste below that will automatically sort the list if I change a number in the list, and then the LARGE and VLOOKUP functions work correctly. The problem is, as you fill out the inventory, the numbers change in the cells that the numbers in my list are referencing, which in turn change the numbers in the list, HOWEVER, since I am not actually changing the numbers myself in the list, it does not autosort.

    I need the list to autosort not just when I manually change a number in the cells in the list, but whenever there is a change in one of the numbers on the list. Here is my current code.

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("K:K")) Is Nothing Then
    Range("K20").Sort Key1:=Range("K21"), _
    Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
    End If
    End Sub



    I would appreciate any help! I uploaded the document as well

    Matthew Skinner
    Attached Files Attached Files

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    718
    Articles
    0
    Excel Version
    Excel 2010 64bit
    The Worksheet_Change event doesn't fire when a cell is changed by code or formula, you need to use the Worksheet_Calculate event.

    Adapting a solution to a similar question at http://stackoverflow.com/questions/1...d-by-a-formula will do the trick.

Posting Permissions

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