WarCaptain
New member
- Joined
- Aug 15, 2014
- Messages
- 1
- Reaction score
- 0
- Points
- 0
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
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
