Formula to shift numbers in a column

jhorvath

I have a column called "Priority" that has my tasks (rows) ranked by importance, with 1 being the top priority and 10 being the lowest priority. When I check off a task I want to delete 1 from the most important task and have that 1 replace that task that is numbered "2". Is there a formula to do this?

Something like if 1 doesn't exist/is deleted, then 2 = 1, 3 = 2, 4 =3, etc. etc.

Thanks in advance!

Paul Newcome

Are you able to provide some screenshots for reference?

jhorvath

smartsheet.JPG

So here I have finshed the task "Surplus". Now I want to delete the "1" that is in the priority column and by doing so I want the task named "sample collection" to now be 1st priority and "maintenance check" to now be priority number 2 and so on. I hope that helps! @Paul Newcome

Paul Newcome

You would need to insert a column that tracks the original priority (manually entered numbers). From there you would use a formula such as the below to subtract how many lower numbers (higher priorities) have been completed.

=[Starting Priority]@row - COUNTIFS([Disposal Of Task]:[Disposal Of Task], @cell <> "", [Starting Priority]:[Starting Priority], @cell < [Starting Priority]@row)

jhorvath

@Paul Newcome Hi! Thanks for the formula. Unfortunately I get a circular reference error. I re-posted this question with a different example and hopefully rephrased my question better. Could you check it out?https://community.smartsheet.com/discussion/86759/ranking-formula#latest

Paul Newcome

Did you insert the additional column and manually enter the starting priority?