As I understand it, you have a set of monthly data which I'll assume span Jan. through Dec., with each month's data listed in a separate column and with a header cell. You want to be able to sort this data set based on the largest to smallest values in a particular month, with the month selected by an entry into a cell in the worksheet.
Although it is possible to produce a sorted display of the values in the data set using worksheet formulas, the data set itself would not be sorted and would need to be unfiltered.
Using VBA will allow a considerably simpler approach that has the added benefit of being able to handle filtered data.
I assume that entry of the month to sort by will be in cell A1
, which has been named sort_month
. The data, including a header for month names, are in columns B
-M
. The data range is named filtered_data
; in my example data, this is the range B1:M25
.
The VBA code that will do the sorting is quite short:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("sort_month")) Is Nothing) Then
Dim theMonth As Long
theMonth = month(DateValue("01-" & Range("sort_month").Value & "-1900"))
Range("filtered_data").Sort Key1:=Cells(1, theMonth + 1), _
Order1:=xlDescending, header:=xlYes
End If
End Sub
This subroutine will run automatically whenever a change is made in sort_month
(i.e., cell A1
). It would make sense to set up data validation on this cell to ensure that only valid month names ("Jan", "Feb", etc.) can be entered into this cell.
The code should not be placed in a standard VBA module, but should instead be attached to the worksheet. The code pane for the worksheet can be accessed by right-clicking the worksheet's tab and selecting "View Code". The code can then be pasted into the pane.
Though it may fall outside your needs, the sortable table can be easily set up for general (i.e., non-developer) use. The version shown below has drop-down data validation for the month entry, table formatting added for readability, and conditional formatting to highlight the sort column.