Is there a way to crate a custom sort order on reports?

Marina Iida
edited 03/10/22 in Smartsheet Basics

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?

  1. Critical
  2. High
  3. Medium
  4. 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

Tags:

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭
    edited 03/10/22

    @Marina Iida

    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([email protected] = "Critical", 1, IF([email protected] = "High", 2, IF([email protected] = "Medium", 3, IF([email protected] = "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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

  • @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.'