0

I have a large data table that has dates across the top. This has filters turned on. I need to sort this data (Largest to smallest) based on the date entered into a user defined field. But I dont know how to make this dynamic.

So say I have 12 colums with the month at the top of each and then a set of numbers below. If I enter Feb in to a specific cell I then want excel to sort by colum Feb from largest to smallest. If I then change the Feb cell to May I want it to sort on May data

Any help please

4
  • 1
    Do you mean you need a column-wise sort?
    – chuff
    May 28, 2013 at 17:45
  • 1
    "Sort data based on the date entered" how exactly?
    – Karan
    May 28, 2013 at 17:46
  • So say I have 12 colums with the month at the top of each and then a set of numbers below. If I enter Feb in to a specific cell I then want excel to sort by colum Feb from largest to smallest. If I then change the Feb cell to May I want it to sort on May data
    – simon
    May 28, 2013 at 18:18
  • What have you already tried and/or researched? What version of Excel are you using? Is the desired effect to sort the entire table or just the specific column?
    – CharlieRB
    May 29, 2013 at 11:55

1 Answer 1

1

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.

formatted sortable table

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .