For work I need to input certain reports into a GIS system, so we have an excel file inlcuding links (to these reports), dates when it was done and more to keep track of what has been done and what still needs to be done. I was tasked with making 1 sheet for us which autosorts the reports first on status (if it has already been done, ongoing, ready for checking or finished) and then on the date the report was filled in. (This is to make it easier for my boss to see what has been done this month) Another sheet is for the external party for which we fill in these reports. They want it sorted on the date the report was posted.

I would like these two sheets to have the same data, and continuously update. I am very much not a coder or in any way good at excel, so any tips or different ideas are welcome

With Chatgpt I eventually made it work quite well through a query and working with a VBA to sort the whole sheet, making a helper colomn to give a number to the status. But the whole sheet would reload every time I made a change in any column, which was very difficult to work with

This was the code Chatgpt gave me

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim ws As Worksheet

Dim laatsteRij As Long



' Check if change is on the "Werkmap" sheet and in the relevant range (A2:Z1500)

If Sh.Name = "Werkmap" And Not Intersect(Target, Sh.Range("A2:AA1500")) Is Nothing Then

    ' Set the worksheet reference

    Set ws = ThisWorkbook.Sheets("Werkmap")

   

    ' Find the last row with data

    laatsteRij = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

   

    ' Add the helper column Z formula if it's not filled

    If IsEmpty(ws.Range("AA2")) Then

        ws.Range("AA2:AA" & laatsteRij).Formula = "=IF(Y2=""Afgerond"", 1, IF(Y2=""Nog voltooien"", 2, IF(Y2=""Nog beginnen"", 3, 4)))"

    End If

   

    ' Sort first by the helper column Z (Status order: Afgerond, Nog voltooien, Nog beginnen)

    ws.Sort.SortFields.Clear

    ws.Sort.SortFields.Add Key:=Range("AA2:AA" & laatsteRij), _

        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal



    ' Then sort by the Date Invoer in column R (from newest to oldest)

    ws.Sort.SortFields.Add Key:=Range("R2:R" & laatsteRij), _

        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

   

    ' Apply the sorting to the entire range

    With ws.Sort

        .SetRange Range("A2:AA" & laatsteRij) ' The range A2:Z1500

        .Header = xlYes

        .Apply

    End With

End If

End Sub

Is there a better way to do this?