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

FA Skills Inventory Template.xlsm (25.6 KB, 8 views)

I am assuming here but it appears that your code works fine. What may not work is when you change a value on the first sheet and then go to see the sort results. This will not change until something is changed in column K. However if you insert your same code on the worksheet activate sub it should work. When you say manually change the numbers I was confused because the numbers in column K are all formulas that if changed you would lose the formula.

Let me know if the worksheet activate fixes the issue.