Improve company productivity with a Business Account.Sign Up

x
?
Solved

Display Sort Order

Posted on 2016-09-15
16
Medium Priority
?
68 Views
Last Modified: 2016-10-07
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
0
Comment
Question by:Jeffrey Coachman
  • 6
  • 3
  • 2
  • +3
16 Comments
 
LVL 9

Expert Comment

by:Koen
ID: 41800458
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
 
LVL 36

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41800518
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
 
LVL 36

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 200 total points (awarded by participants)
ID: 41800540
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 52

Assisted Solution

by:Martin Liss
Martin Liss earned 800 total points (awarded by participants)
ID: 41800664
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
 
LVL 36

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41800697
Nice one Martin! :)
0
 
LVL 52

Expert Comment

by:Martin Liss
ID: 41800711
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
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41800724
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
 
LVL 74

Author Comment

by:Jeffrey Coachman
ID: 41800808
@Subodh Tiwari (Neeraj)
Oddity with your formula?
Sorting by LastName always displays "FirstName"
oddity
0
 
LVL 74

Author Comment

by:Jeffrey Coachman
ID: 41800809
@Martin Liss
Works good, ...thanks
0
 
LVL 36

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41801011
@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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41802409
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
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41802439
Glenn,

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

Wayne
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41802456
got it to work.  cool function.
0
 
LVL 36

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41802624
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
 
LVL 48

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 1000 total points (awarded by participants)
ID: 41803908
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
 
LVL 36

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41833333
The chosen answers resolved the question.
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question