Is there a way to crate a custom sort order on reports?
I have a report that includes a "Priority" column (single select - Critical, High, Medium, or Low). When I use the sorting function in the sheet, I think it sorts in the order of the list that I typed in the column properties.
However, when you're in a report, the sorting only allows for ascending or descending (numerically/alphabetically).
Is there a way to create a custom sort, so it goes in the following order?
- Critical
- High
- Medium
- Low
The only way I've been able to solve for this is to number these, so it always sorts from 1 to 4.
Thanks,
Marina
Answers
-
That's the trade-off for being able to sort in list order in the sheet...
If you want to have just the text values in your list, but also be able to sort in list order on the report, consider adding a hidden helper column on your sheet. Use nested IFs to populate this column with a number based on the selected priority:
=IF(Priority@row = "Critical", 1, IF(Priority@row = "High", 2, IF(Priority@row = "Medium", 3, IF(Priority@row = "Low", 4, ""))))
Then sort your report ascending by the helper column. You can hide the helper column in your report as well as in the sheet itself.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman Thank you so much! That's a great solution. I remember seeing that used somewhere else, but completely forgotten about it.
Cheers!
-
My issue with many of these questions and answers is that often the solution to a basic request - something similar to a basic excel capability - is to 'add a helper column.' I've found questions from 3-5 years ago for a number of my situations and the issue still remains. I have nothing for this specific thread other than I'm tired of having to add 'helper columns.'
-
this 100%
