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?
-
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)
-
@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
-
Did you insert the additional column and manually enter the starting priority?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.6K Get Help
- 63 Global Discussions
- 68 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!