Ranking Formula

jhorvath ✭✭
edited 01/20/22 in Formulas and Functions

Hello all,

I have a sheet with columns that look like this:

Spot in Queue Case Number Case Prep Completed Case Completed

1 100 x

2 101 x

3 102

Essentially, what I would like to do is change the spot in the queue when the case is completed. For example, when I check off 'Case Completed' for case number 100, the spot in the queue will be blank and case 101 will now be spot 1 in the queue, and case 102 will be spot number 2.

Is there any way to do this? Thank you all!


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @jhorvath

    The way I would do this is to set up a second Archive sheet to automatically move rows to once they've been marked as Completed (see: Automatically Move or Copy Rows Between Sheets)

    That way your sheet only has the current cases in the queue.

    Then in regards to assigning a number, you could use an Auto-Number column to automatically add a number to each row (for example, titled "Auto"). If you want it to then re-start at 1 once a row is moved, you can set up a second text/number column with the following column formula:

    =MATCH(Auto@row, Auto:Atuo, 0)

    Let me know if this makes sense and will work for you!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!