I've been unable to directly find how to do something in Excel that I can do with the Sort menu but can't seem to find out how to via a formula that will update my sorting as I change a given value.

My table looks something like this

So what I'm wanting to do is have my ticket columns dynamically sorted by most recent Due Date to least recent and then to sort by the Last Update field from oldest to most recent for shared Due Dates.

I can do that simply in the Sort menu by choosing to sort left to right in the options menu but everything I can find to try to figure out how to do this assumes that the first row is static instead of the first column (even making a table here assumed a header row and not whatever a column version of that would be called) and maybe it's just me but I can't seem to find a way to adapt the formulas I'm finding to make this work correctly.

End of the day I can just click the Sort button and OK, but I would just like it to update as I make changes to my Due Dates. Any help would be appreciated.

CodePudding user response：

Put this in the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim lastclm As Long
        lastclm = Cells(1, Columns.Count).End(xlToLeft).Column
        
        
        Dim rng As Range
        Set rng = Range(Cells(1, 2), Cells(5, lastclm))
        
        If Intersect(rng, Target) Is Nothing Then Exit Sub
        
        On Error GoTo safeout
        
        Application.EnableEvents = False
        
        Dim kyRng As Range
        Set kyRng = Range(Cells(3, 2), Cells(3, lastclm))
        
        With Me.Sort
            .SortFields.Clear
            .SortFields.Add2 Key:=kyRng, Order:=xlAscending
            .SetRange rng
            .Orientation = xlLeftToRight
            .Apply
        End With
    
    safeout:
        
        Application.EnableEvents = True
        
    End Sub

Now as things change in the table it will sort on the third row left to right automatically.

CodePudding user response：

If it isn't necessary to do the sorting in-place, you can just use Sortby:

=SORTBY(B1:D5,B3:D3,-1,B4:D4,1)