Formula to shift numbers in a column

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!

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide some screenshots for reference?

    thinkspi.com

  • 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
    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)

    thinkspi.com

  • @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
    Paul Newcome ✭✭✭✭✭✭

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

    thinkspi.com

Help Article Resources