Responding to Requests in the Order they are Recieved Formula


I have a request form that is sorted by date requested.

I have a # column that begins at 1..... Each week I run a report and email each person what # they are in line and the estimated response date.

I'm trying to come up with a formula that recalibrates the ordered list when I finish a job. The #2 spot now becomes #1 and so on down the line.

It's pretty simpel to order with the =Order3+1. Is there a way to automate this?

Automation: Trigger- STATUS = COMPLETE, Order = Order-1


  • Courtney S.
    Courtney S. ✭✭✭✭✭
    edited 04/11/24

    If you have an Auto-Number column that numbers rows in the order they were created, I think you might be able to create a column formula for this purpose with the RANKEQ and COLLECT functions.

    Auto-number rows | Smartsheet Learning Center

    RANKEQ Function | Smartsheet Learning Center

    COLLECT Function | Smartsheet Learning Center

    RANKEQ won't work on dates, it needs to rank numbers, so that's why I think an auto-number column would help.

    The COLLECT function would be the Range portion of the RANKEQ function. In the COLLECT function, your range would be the auto-number column, your criterion range would be the column where you mark rows as incomplete or complete, and your criterion would be the answer in that column that marks the row as incomplete. So COLLECT will collect together the auto-number answers only for the rows that are incomplete, and then RANKEQ will rank them.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!