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!

Find more posts tagged with

Formulas

Conditional Formatting

Accepted answers

All comments

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.