Prioritize Order of Record Fulfillment

khankoff
khankoff ✭✭✭✭✭
edited 12/09/19 inFormulas and Functions

I have a list of records in a worksheet, and would like for each of them to be prioritized 1 through ### (depending upon how many are in the list). Each record needs to have a unique priority, because the order in which the record will be acted upon by an Owner will be determined by where it is ranked in the prioritization. Therefore the number can't be used more than once in the list.

这个可以做吗?如何?通过一个公式吗?Need some guidance.

Thank you.

Tags:

评论

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi,

    Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too,[email protected])

    有一个美妙的周末!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would need to be able to clearly define your criteria to be able to put this into a formula for an automation. If you are able to define that and maybe provide some screenshots, a solution could be found for you.

  • khankoff
    khankoff ✭✭✭✭✭

    Hi Andree,

    I don't have any useful screenshots, as I'm still building the solution. However, if you picture a worksheet with three columns:

    Column 1: Data Server

    Column 2: Data Path

    Column 3: Data Scan Priority

    There could be many many records in the worksheet, and the goal is to provide the an order in which the records should be Data Scanned (scanning is a separate process). So if the first three records in the list had a Data Scan Priority of 2,3,1, then the third record would actually be scanned first, the first record scanned second, and the second record scanned third.

    I'd like to have a way of preventing someone from entering the first three records as something like 1, 2, 1, because having two 1's would confuse the team responsible for performing the data scan (they would have to resolve which of the two 1's in the list was actually a priority).

    Just wondering if this could be done programmatically.

    Ken

  • khankoff
    khankoff ✭✭✭✭✭

    Thanks Paul, but I'm not trying to have a formula actually set the priority. Just wondering whether it would be possible to prevent someone from entering the same priority number (say 5th in the list) more than once.

    The goal is to use the data scan priority as the order in which data records are scanned (scanning is a separate process). So the formula would not allow 1,2,3,4,5,5,5,6,7,8,9,etc. It would not be possible to enter 5 (or any other number currently in use) more than once.

    Ken

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Unfortunately you cannot accomplish exactly what you are looking for within Smartsheet. All you can do is use a formula to flag a duplicate entry and then use Alerts and/or Update Requests and Conditional formatting to bring it to their attention for correction.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Ok.

    I saw that Paul answered.

    Let me know if I can help with anything else.

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!
Hi<\/p>

I believe this formula, entered in the TITLE (Formula) column would work for you:<\/p>

=IF(ISBLANK([INT \/ DOM \/ TEMP \/ NG]@row), \"//www.santa-greenland.com/community/discussion/43701/\", TITLE$1)<\/p>


<\/p>

It creates this:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/p>

You start with an IF<\/p>

For the logical expression you use ISBLANK<\/p>

ISBLANK([INT \/ DOM \/ TEMP \/ NG]@row) is checking that the cell in the column INT \/ DOM \/ TEMP \/ NG is blank<\/p>

If that is true it puts in \"//www.santa-greenland.com/community/discussion/43701/\" (ie nothing)<\/p>

If it is false it puts in the value in the TITLE column, but instead of TITLE@row, it pulls just from row 1 because of the $1<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":112287,"type":"question","name":"Count two columns of a sheet that each match specific criteria","excerpt":"Hello, I am trying to create a count that both matches one of 4 specific project initiatives and shows approved in another column. Example: Lead by Example (One Column) and the Status is \"Approved\" in another column.","snippet":"Hello, I am trying to create a count that both matches one of 4 specific project initiatives and shows approved in another column. Example: Lead by Example (One Column) and the…","categoryID":322,"dateInserted":"2023-10-27T18:08:34+00:00","dateUpdated":null,"dateLastComment":"2023-10-27T20:42:18+00:00","insertUserID":169195,"insertUser":{"userID":169195,"name":"Bob Schweighart","url":"https:\/\/community.smartsheet.com\/profile\/Bob%20Schweighart","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:41:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":169195,"lastUser":{"userID":169195,"name":"Bob Schweighart","url":"https:\/\/community.smartsheet.com\/profile\/Bob%20Schweighart","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:41:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":22,"score":null,"hot":3396870652,"url":"https:\/\/community.smartsheet.com\/discussion\/112287\/count-two-columns-of-a-sheet-that-each-match-specific-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112287\/count-two-columns-of-a-sheet-that-each-match-specific-criteria","format":"Rich","lastPost":{"discussionID":112287,"commentID":402120,"name":"Re: Count two columns of a sheet that each match specific criteria","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402120#Comment_402120","dateInserted":"2023-10-27T20:42:18+00:00","insertUserID":169195,"insertUser":{"userID":169195,"name":"Bob Schweighart","url":"https:\/\/community.smartsheet.com\/profile\/Bob%20Schweighart","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:41:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-27T21:43:37+00:00","dateAnswered":"2023-10-27T20:13:32+00:00","acceptedAnswers":[{"commentID":402113,"body":"

@Bob Schweighart<\/a> you just need the COUNTIFS formula and put those 2 criterion in it. Are they distinct counts for the 4 different projects? If so, that is easier. If you are counting all 4, then you need to decide on an imbedded OR or a NOT.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":112242,"type":"question","name":"Can I use Index\/Match and Left functions in one formula?","excerpt":"Hello, can someone help me with the left function within the index\/match? This is what I tried but it always returns NO MATCH: =INDEX({Master Job SO}, MATCH(LEFT([Job Number]@row,5), {Master Job #}, 0)) I also tried creating a helper column, Job Prefix, using =LEFT([Job Number]@row, 5) and using that result in the SO…","snippet":"Hello, can someone help me with the left function within the index\/match? This is what I tried but it always returns NO MATCH: =INDEX({Master Job SO}, MATCH(LEFT([Job…","categoryID":322,"dateInserted":"2023-10-26T22:39:07+00:00","dateUpdated":null,"dateLastComment":"2023-10-27T17:46:03+00:00","insertUserID":157316,"insertUser":{"userID":157316,"name":"amber.lange","url":"https:\/\/community.smartsheet.com\/profile\/amber.lange","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:44:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":157316,"lastUser":{"userID":157316,"name":"amber.lange","url":"https:\/\/community.smartsheet.com\/profile\/amber.lange","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:44:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":33,"score":null,"hot":3396791110,"url":"https:\/\/community.smartsheet.com\/discussion\/112242\/can-i-use-index-match-and-left-functions-in-one-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112242\/can-i-use-index-match-and-left-functions-in-one-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":112242,"commentID":402089,"name":"Re: Can I use Index\/Match and Left functions in one formula?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402089#Comment_402089","dateInserted":"2023-10-27T17:46:03+00:00","insertUserID":157316,"insertUser":{"userID":157316,"name":"amber.lange","url":"https:\/\/community.smartsheet.com\/profile\/amber.lange","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:44:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-27T17:46:07+00:00","dateAnswered":"2023-10-27T17:30:20+00:00","acceptedAnswers":[{"commentID":402081,"body":"

There we go. The [SO] column looks like it is being manually populated which is storing the data as numerical data, but the LEFT function outputs text.<\/p>


<\/p>

Insert a column on the reference sheet and use<\/p>

=SO@row + \"//www.santa-greenland.com/community/discussion/43701/\"<\/p>

plus quote quote<\/p>


<\/p>

This will convert all entries into a text string so that data types match. You would then match on this helper column in your INDEX\/MATCH.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions