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

Are you able to provide some screenshots for reference?
Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!
If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

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

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)
Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!
If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

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

Did you insert the additional column and manually enter the starting priority?
Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!
If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.
Help Article Resources
Categories
Check out the Formula Handbook template!