?
Solved

Display Sort Order

Posted on 2016-09-15
16
Medium Priority
?
65 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 32

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 32

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

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 32

Expert Comment

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

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
 
LVL 74

Author Comment

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

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 32

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 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 32

Expert Comment

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

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

801 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