Ranking Formula

jhorvath

ex.JPG

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.

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!

Cheers,

Genevieve