Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2015
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can an Excel sheet show a description of the custom sort levels in the header/footer?

    I have various Excel sheets containing the same information, but I have created custom sorts for each sheet.

    When viewing any sheet in a Page layout view, I am able to see any information I have included in the header or footer.

    I would like to be able to view in the header or footer the custom sort levels for any particular sheet. For example: Sheet A sorts by Project, then by Date, then by Start Time. Sheet B sorts by Start Time, then by Project, then by Date. All sort on "Values", but the Order of the sorted levels varies - some levels sort A-Z; some sort Z-A.

    Questions:
    1. Is there a way to show in the header or footer the custom sort levels used for any particular sheet?
    2. If so, how much of the sort information can be included?

    Thanks.

    mburke

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    mburke,

    Assuming you had 3 columns with headers: Project, Date, StartTime. You create a named range for each column (not including the header) each with the same name as the header for the column. Ex. A2:A20 is named Project, B2:B20 is named Date, and C2:C20 is named StartTime. There is a fourth named range called Matrix which is the range of all the data including the header, A1:C20

    The following code will sort in the order of Project, Date, StartTime then place the sort criteria in the header for the sheet.

    Page Layout View:
    SortInHeader.png

    Code:
    Sub SortCriteriaInHeader()
    '-------------------------
    'SORT SHEET USING CRITERIA
    With ActiveSheet
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("Project"), Order:=xlAscending
        .Sort.SortFields.Add Key:=Range("Date"), Order:=xlAscending
        .Sort.SortFields.Add Key:=Range("StartTime"), Order:=xlAscending
        With .Sort
            .SetRange Range("Matrix")
            .Header = xlYes
            .Apply
        End With
    '-------------------------
    'WRITE CRITERIA TO HEADER
        With .PageSetup
            .LeftHeader = " Sort Range: " & Range("Matrix").Address & Chr(10) & _
                          " First Sort: " & Range("Project").Offset(-1, 0).Cells(1, 1) & Chr(10) & _
                          "Second Sort: " & Range("Date").Offset(-1, 0).Cells(1, 1) & Chr(10) & _
                          " Third Sort: " & Range("StartTime").Offset(-1, 0).Cells(1, 1)
        End With
    End With
    End Sub
    HTH,
    Maud

  3. #3
    New Lounger
    Join Date
    Sep 2015
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Maud,

    Thanks again for your help. I will need to carve out some time to review your suggestion, and hope to do so in the near future. I just wanted to let you know I do appreciate all the time/help you are giving me for this question.

    Regards,

    Mike.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Mike,

    Let me know if you need help setting it up in your project for a test

    Maud

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •