Numerical "Priority" Method

Jess Bowles

Hello,

I am in need of a method for assigning priority of a submitted row 1-10. If 1 is selected, the previous 1 needs to become a 2, the 2 becomes a 3, etc. The column needs to recalculate when a row is complete and the 'priority' column becomes blank. I don't think this would be possible without using many columns to determine what needs to be changed, so I came here to see if anyone has a better way to complete this.

Thank you for your time.

Paul Newcome

Insert an auto-number column (I called mine "Auto" in this example) with no special formatting.

Then the formula to output the adjusted priority is along the lines of...

=IF([Submitted Priority]@row <> "", COUNTIFS([Submitted Priority]:[Submitted Priority], @cell < [Submitted Priority]@row) + COUNTIFS([Submitted Priority]:[Submitted Priority], @cell = [Submitted Priority]@row, Auto:Auto, @cell >=[email protected]))

Paul Newcome

Insert an auto-number column (I called mine "Auto" in this example) with no special formatting.

Then the formula to output the adjusted priority is along the lines of...

=IF([Submitted Priority]@row <> "", COUNTIFS([Submitted Priority]:[Submitted Priority], @cell < [Submitted Priority]@row) + COUNTIFS([Submitted Priority]:[Submitted Priority], @cell = [Submitted Priority]@row, Auto:Auto, @cell >=[email protected]))

Jess Bowles

Insert an auto-number column (I called mine "Auto" in this example) with no special formatting.<\/p>

Then the formula to output the adjusted priority is along the lines of...<\/p>

=IF([Submitted Priority]@row <> "", COUNTIFS([Submitted Priority]:[Submitted Priority], @cell < [Submitted Priority]@row) + COUNTIFS([Submitted Priority]:[Submitted Priority], @cell = [Submitted Priority]@row, Auto:Auto, @cell >= Auto@row))<\/p>","bodyRaw":"[{\"insert\":\"Insert an auto-number column (I called mine \\\"Auto\\\" in this example) with no special formatting.\\n\\nThen the formula to output the adjusted priority is along the lines of...\\n=IF([Submitted Priority]@row <> \\\"\\\", COUNTIFS([Submitted Priority]:[Submitted Priority], @cell < [Submitted Priority]@row) + COUNTIFS([Submitted Priority]:[Submitted Priority], @cell = [Submitted Priority]@row, Auto:Auto, @cell >= Auto@row))\\n\"}]","format":"rich","dateInserted":"2022-05-18T13:45:39+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-23T19:11:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/330064#Comment_330064","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/330064#Comment_330064

Paul,

Thank you so much! This is working perfectly.

Paul Newcome

Happy to help.️

CJTimm

Hi@Paul NewcomeI am having a hard time getting the formula above to work on my sheet. I even named my columns the same as yours for the example. Any Ideas?

Formula I'm using : =IF([Submitted Priority]@row <> "", COUNTIFS([Submitted Priority]:[Submitted Priority], @cell < [Submitted Priority]@row) + COUNTIFS([Submitted Priority]:[Submitted Priority], @cell = [Submitted Priority]@row,

Auto:Auto, @cell >=[email protected]))

Error: #Circular Reference

Is the "auto" number supposed to be my priority number, or the Submitted Priority column? Would I hide the other?

I'm missing something simple, I know it.

Paul Newcome

@CJTimmThe Submitted Priority column is the column that houses the manually entered priorities. The formula would then go in a different column. This other column with the formula in it would be the one that shows the new priority.

Jess Bowles

@CJTimmPaul is correct. Check out how our sheet looks, and just ignore the string in entry 11 (You should definitely ensure that 'submitted priority' is a dropdown restricted to list values.) To reiterate@Paul Newcome's point, the formula goes in a third column.