Solved

Display Sort Order

Posted on 2016-09-15
16
49 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 8

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 28

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 28

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 50 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
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 200 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 28

Expert Comment

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

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 47

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 74

Author Comment

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

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 47

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 28

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 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 250 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 28

Expert Comment

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now