Display Sort Order

I need for a cell in a sheet (in this case, ...cell F1) to display the Sort Order of a list.
Currently, users can see the Sort Order if I turn on the filtering (dropdowns), ...However some user will not notice the small sort arrows,
...and also the sort/filter dropdowns do not appear when the list is printed. (which the typical way users will see/use this list)

In other words, ...I want something like this:
Sort in Worksheet...To appear like this when printed:
Viewed in printout
Notice that in both images, I would like cell F1 to display the Sort.
So if the list was resorted by Last Name, ....Cell  F1 would display: LastName

Notes:
The sort "Order" (direction) is not really needed, just the field name.
The list will probably never be sorted on more than one column at a time, ...so no need to worry about that either

Sample spreadsheet included for clarity.
Any questions, ...let me know
Thanks

JeffCoachman
EE--Excel-Sort-Q.xlsx
LVL 74
Jeffrey CoachmanMIS LiasonAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Wayne Taylor (webtubbs)Connect With a Mentor AstronautCommented:
Thanks Guys. Slight change to the function to show the sort order. There's now an optional Boolean parameter specifying whether the sort order is required to be displayed.

Function GetSortField(Optional ShowSortOrder As Boolean = False)

    Application.Volatile
    With Application.Caller.Parent.AutoFilter.Sort.SortFields
        If .Count = 0 Then
            GetSortField = CVErr(xlErrNA)
        Else
            GetSortField = .Item(1).Key.Value
            If ShowSortOrder Then
                GetSortField = GetSortField & IIf(.Item(1).Order = 1, " [ASC]", " [DESC]")
            End If
        End If
    End With
    
End Function

Open in new window

0
 
KoenCommented:
I don't think there is a way to 'trap' the sort order as set by the built in filtering function...(at least I have never heard of it).

A way you could work around it is to provide a 'button' (checkbox choice or combo box or something) which then executes the sort (and since it was selected, can also be displayed).

Unless if someone has a better idea...
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try this Array Formula in F1 which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

In F1
=IF(SUM(--(A2:A31>A3:A32))=1,"FirstName",IF(SUM(--(B2:B31>B3:B32))=1,"LastName",IF(SUM(--(C2:C31>C3:C32))=1,"Extension",IF(SUM(--(D2:D31>D3:D32))=1,"Dept",""))))

Open in new window

The above formula will detect if a column is sorted in Ascending order.
For more details, refer to the attached.
EE--Excel-Sort-Q.xlsx
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
The following formula will work for both the Ascending or Descending order.
In F1
=IF(OR(SUM(--(A2:A31>A3:A32))=1,SUM(--(A2:A31<A3:A32))=0),"FirstName",IF(OR(SUM(--(B2:B31>B3:B32))=1,SUM(--(B2:B31<B3:B32))=0),"LastName",IF(OR(SUM(--(C2:C31>C3:C32))=1,SUM(--(C2:C31<C3:C32))=0),"Extension",IF(OR(SUM(--(D2:D31>D3:D32))=1,SUM(--(D2:D31<D3:D32))=0),"Dept",""))))

Open in new window

Confirm with Ctrl+Shift+Enter.
EE--Excel-Sort-Q-v2.xlsx
0
 
Martin LissConnect With a Mentor Older than dirtCommented:
If a VBA solution is acceptable then try this workbook. In it I've changed the data to a table and added a formula in cell I5 that could be anyplace on the sheet either hidden or out of sight. The code can also tell you if the sort is ascending or descending if you like.
28970158.xlsm
1
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Nice one Martin! :)
0
 
Martin LissOlder than dirtCommented:
Thanks but I really can't take credit for the code that examines the sort keys.  I added the URL to the original code in the workbook.
0
 
Wayne Taylor (webtubbs)AstronautCommented:
You can use this function to return the sort field of the Autofilter...

Function GetSortField()

    Application.Volatile
    With Application.Caller.Parent.AutoFilter.Sort.SortFields
        If .Count = 0 Then
            GetSortField = CVErr(xlErrNA)
        Else
            GetSortField = .Item(1).Key.Value
        End If
    End With
    
End Function

Open in new window

2
 
Jeffrey CoachmanMIS LiasonAuthor Commented:
@Subodh Tiwari (Neeraj)
Oddity with your formula?
Sorting by LastName always displays "FirstName"
oddity
0
 
Jeffrey CoachmanMIS LiasonAuthor Commented:
@Martin Liss
Works good, ...thanks
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Subodh Tiwari (Neeraj)
Oddity with your formula?
Sorting by LastName always displays "FirstName"
If you look at your sample data, you will see that the FirstName is the first column which is sorted in ascending order though the sorting is applied on the LastName field.
In reality no such names exist and if you replace your dummy data with your actual data, it will work without an issue.
0
 
Glenn RayExcel VBA DeveloperCommented:
Question closing discussion aside, @Wayne, I couldn't get your function to work in the OP example workbook.  Maybe some argument passing is missing?
0
 
Wayne Taylor (webtubbs)AstronautCommented:
Glenn,

No arguments are required. Just an autofilter on the same worksheet. And then one of the fields sorted.

Wayne
0
 
Glenn RayExcel VBA DeveloperCommented:
got it to work.  cool function.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Yeah I agree. Wayne provided very smart solution and I am surprised that OP didn't say a single word about this solution. Maybe OP was not clear how to use it.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The chosen answers resolved the question.
0
All Courses

From novice to tech pro — start learning today.