Translate Cell value to a specific number

Allen4480
Allen4480 ✭
inFormulas and Functions

Hello,

My Master sheet has one column called "Portfolio,". Each cell in that column *may* contain multiple values - up to5choices (as shown below)

I'd like a new column that would convert those "names" to a specified number (ie. 1,2,3,4,5)

for example (using below as an example), it would translate to....

1

1,2,3,4,5

1,2,3

1,2,3,4,5

1,3

Screenshot 2023-03-27 120215.jpg


Do I need to do this using a report, or help column?

thanks, tons in advance,

· Share on Facebook Share on Twitter

Best Answer

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!
Hi @Hobo<\/a> ,<\/p>


<\/p>

You can use this formula. The formula will count rows where priority is high and status is not completed.<\/p>


<\/p>

=COUNTIFS(Priority:Priority, \"High\", Status:Status, <> \"Completed\")<\/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":112197,"type":"question","name":"Method to check whether a Assessment Date has passed upon multiple cells within a row.","excerpt":"Good afternoon, We are currently using a smart sheet to track assessment bookings for different standards. We enter the assessment dates within the relevant cells for a particular standard (normally 3 assessments per standard, however are named differently which has resulted in a large number of columns which is…","snippet":"Good afternoon, We are currently using a smart sheet to track assessment bookings for different standards. We enter the assessment dates within the relevant cells for a particular…","categoryID":322,"dateInserted":"2023-10-26T13:07:42+00:00","dateUpdated":null,"dateLastComment":"2023-10-27T10:08:35+00:00","insertUserID":143323,"insertUser":{"userID":143323,"name":"Matthew Drake","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Matthew%20Drake","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T11:37:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":143323,"lastUser":{"userID":143323,"name":"Matthew Drake","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Matthew%20Drake","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T11:37:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":54,"score":null,"hot":3396731177,"url":"https:\/\/community.smartsheet.com\/discussion\/112197\/method-to-check-whether-a-assessment-date-has-passed-upon-multiple-cells-within-a-row","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112197\/method-to-check-whether-a-assessment-date-has-passed-upon-multiple-cells-within-a-row","format":"Rich","lastPost":{"discussionID":112197,"commentID":401973,"name":"Re: Method to check whether a Assessment Date has passed upon multiple cells within a row.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401973#Comment_401973","dateInserted":"2023-10-27T10:08:35+00:00","insertUserID":143323,"insertUser":{"userID":143323,"name":"Matthew Drake","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Matthew%20Drake","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T11:37:37+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-27T10:10:19+00:00","dateAnswered":"2023-10-26T14:43:35+00:00","acceptedAnswers":[{"commentID":401827,"body":"

@Matthew Drake<\/a> <\/p>

If you wanted to build a nested IF statement, you would add RYG ball Symbol column and use a formula something like this:<\/p>

=IF(AND(TODAY() > [Knowledge\/SJT\/MCQ\/Skills Test Date & Time]@row, ISBLANK([Knowledge Test Result]@row)), \"Red\", IF(AND(TODAY() > [Project \/ Showcase Portfolio]@row, ISBLANK([Project \/ Showcase Portfolio Results]@row)), \"Red\", \"Green\"))<\/p>

You could keep building this out to evaluate all of your date \/ result fields, but you could run into issues where a date is not yet entered.<\/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":112223,"type":"question","name":"How to pull the most recent status symbol from another sheet?","excerpt":"Hello, I am trying to pull the most recent status symbol based off of the most recent date to another sheet. Basically, I am trying to get the yellow status symbol from the 20th to pull into another sheet and once someone creates a new update with the most recent date\/symbol, that symbol should be automatically updated…","snippet":"Hello, I am trying to pull the most recent status symbol based off of the most recent date to another sheet. Basically, I am trying to get the yellow status symbol from the 20th…","categoryID":322,"dateInserted":"2023-10-26T17:10:42+00:00","dateUpdated":null,"dateLastComment":"2023-10-26T20:32:57+00:00","insertUserID":169134,"insertUser":{"userID":169134,"name":"Brittaney Pizzato","url":"https:\/\/community.smartsheet.com\/profile\/Brittaney%20Pizzato","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-26T20:42:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":133754,"lastUser":{"userID":133754,"name":"Scott Peters","url":"https:\/\/community.smartsheet.com\/profile\/Scott%20Peters","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/QK9YYA22R88Y\/nEZ19DQAQ4262.png","dateLastActive":"2023-10-26T20:33:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":22,"score":null,"hot":3396693219,"url":"https:\/\/community.smartsheet.com\/discussion\/112223\/how-to-pull-the-most-recent-status-symbol-from-another-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112223\/how-to-pull-the-most-recent-status-symbol-from-another-sheet","format":"Rich","lastPost":{"discussionID":112223,"commentID":401926,"name":"Re: How to pull the most recent status symbol from another sheet?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401926#Comment_401926","dateInserted":"2023-10-26T20:32:57+00:00","insertUserID":133754,"insertUser":{"userID":133754,"name":"Scott Peters","url":"https:\/\/community.smartsheet.com\/profile\/Scott%20Peters","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/QK9YYA22R88Y\/nEZ19DQAQ4262.png","dateLastActive":"2023-10-26T20:33:13+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\/F30QLRHNUSZM\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/F30QLRHNUSZM\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/F30QLRHNUSZM\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/F30QLRHNUSZM\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/F30QLRHNUSZM\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/F30QLRHNUSZM\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-26T20:42:55+00:00","dateAnswered":"2023-10-26T20:32:57+00:00","acceptedAnswers":[{"commentID":401926,"body":"

Hi @Brittaney Pizzato<\/a> - You would match the max date against the date column, and you don't need Collect in this scenario, so it could read as =INDEX({Project Health}, MATCH(MAX({Date}), {Date}, 0))<\/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":[]}],"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

  • Countifs formula

    嗨,团队对寻求一些帮助n, i need a countifs formula to count all the high priority actions (Priority column) that are currently either not started or ongoing (Status column) Thanks in advance

  • Method to check whether a Assessment Date has passed upon multiple cells within a row.

    Good afternoon, We are currently using a smart sheet to track assessment bookings for different standards. We enter the assessment dates within the relevant cells for a particular standard (normally 3 assessments per standard, however are named differently which has resulted in a large number of columns which is…

  • How to pull the most recent status symbol from another sheet?

    Hello, I am trying to pull the most recent status symbol based off of the most recent date to another sheet. Basically, I am trying to get the yellow status symbol from the 20th to pull into another sheet and once someone creates a new update with the most recent date/symbol, that symbol should be automatically updated…