Ranking Formula

jhorvath
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!

Answers

  • 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([email protected], Auto:Atuo, 0)

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

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!