Can you use 2 joins in the same formula

IF Type = Project join Type+ID+"-"+PMO Project Name

If Type = Request join Type+ID+"-"+Request Name


I've tried this a couple of ways - if I select Request - it comes up as Request1000-RequestName

If I select Request then it blanks out. instead of Project1000-ProjectName


Formula

=IF(Type@row = "Rqst", JOIN(Type@row + [Rqst ID]@row + "-" + [Request Name]@row, IF(Type@row = "Proj", JOIN(Type@row + [Rqst ID]@row + "-" + [PMO Project Name]@row))))


ultimately I want to put in if(ands( for various conditions but can't get this basic formula to work. thanks so much for your help.

Best Answers

  • Bruce Johnson
    Bruce Johnson ✭✭✭✭
    Answer ✓

    Hi L@123 - thanks for the nudge - I need that sometimes - your guidance worked and is much appreciated!

Answers

  • L_123
    L_123 ✭✭✭✭✭✭

    couple issues going on here. you have some parenthesis wrong, but i'll slide by that to the more pressing part,

    join is for making a range concatenate, not individual values.


    given the column names are repeated in the cells

    =Join(A@row:D@row, ", ")

    would give

    a, b, c, d

    if you just wish to concatenate you can simply add text values

    =A@row + ", " + B@row + "," + C@row + ", " + D@row

    would result in the same thing

    a, b, c, d

  • Bruce Johnson
    Bruce Johnson ✭✭✭✭

    Thank you for the reply - what will allow me to do what I want to do? the exercise isn't to make the formula that I provided correct . I do know how to join A+B+C columns the exercise is to make if project is selected then I want the name to be project+ID+"-"+-projectname of Request is selected want the name to be request+ID+"-"+requestname


    project and request is a drop down

    the project name is in its own column

    request name is it's own column.

    so there needs to be a condition before -concatenation of the fields

    so when project is selected then the outcome I want is project12345-projectname

    When a request is selected the outcome would be request12346-requestname

    so on and so forth


    thanks you

  • Bruce Johnson
    Bruce Johnson ✭✭✭✭
    Answer ✓

    Hi L@123 - thanks for the nudge - I need that sometimes - your guidance worked and is much appreciated!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Hey friend!<\/p>

I hope this solves the problem. The problem is so granular, you'll laugh. Look at the quotation marks around Not Started and see how they are different than what's around Red and Green. <\/p>

The quotation marks around Not Started are curly and need to be straight like with Red and Green. Maybe you were tinkering with the formula in a text editor or word docs. Anyway, I hope this solves the problem. Good Luck and please let me know, if this solved it. <\/p>

=IF(AND([Status]@row = \"Not Started\", [Start]@row <= TODAY()), \"Red\", \"Green\")<\/p>

thanks<\/p>

Michael<\/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":111670,"type":"question","name":"Check Box Selected \/ Not Selected = YES or No","excerpt":"High Smartsheets Community, Question. Is there a formula for if a check box in a column is selected that it would return an answer of \"yes\" in another column and vice versa, if a check box is not selected it would return an answer of \"no.\" I know I can do a drop down, but there are some other factors that require me to…","snippet":"High Smartsheets Community, Question. Is there a formula for if a check box in a column is selected that it would return an answer of \"yes\" in another column and vice versa, if a…","categoryID":322,"dateInserted":"2023-10-13T21:29:00+00:00","dateUpdated":null,"dateLastComment":"2023-10-14T00:32:21+00:00","insertUserID":147643,"insertUser":{"userID":147643,"name":"Anthony DAmbrosio","url":"https:\/\/community.smartsheet.com\/profile\/Anthony%20DAmbrosio","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-14T00:27:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":147643,"lastUser":{"userID":147643,"name":"Anthony DAmbrosio","url":"https:\/\/community.smartsheet.com\/profile\/Anthony%20DAmbrosio","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-14T00:27:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":24,"score":null,"hot":3394477281,"url":"https:\/\/community.smartsheet.com\/discussion\/111670\/check-box-selected-not-selected-yes-or-no","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111670\/check-box-selected-not-selected-yes-or-no","format":"Rich","lastPost":{"discussionID":111670,"commentID":400020,"name":"Re: Check Box Selected \/ Not Selected = YES or No","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400020#Comment_400020","dateInserted":"2023-10-14T00:32:21+00:00","insertUserID":147643,"insertUser":{"userID":147643,"name":"Anthony DAmbrosio","url":"https:\/\/community.smartsheet.com\/profile\/Anthony%20DAmbrosio","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-14T00:27:43+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-14T00:31:43+00:00","dateAnswered":"2023-10-13T21:43:32+00:00","acceptedAnswers":[{"commentID":400009,"body":"

Hi @Anthony DAmbrosio<\/a> <\/p>

Checkboxes use a 1 for True (yes) and a 0 for False (no). <\/p>

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

Formula: =IF(Chkbx@row = 1, \"Yes\", \"No\")<\/p>

Change \"Chkbx\" to your column name. If your column name is more than one word it will need to be wrapped in brackets like this: [Column Name]<\/p>

You can make this a column formula so that it occurs on every row.<\/p>

Hope this helps!<\/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":111658,"type":"question","name":"How do I create an CountIF statement to include multiple selections within a cell?","excerpt":"I'm creating sheet summary calculations to tally the total count of a specific dropdown menu item; however, I am using multi-select. I know how to use a standard COUNTIF formula, but it only returns the total count if the option is the only one selected. Here is the formula I'm using: =COUNTIF([Level III Request]:[Level…","snippet":"I'm creating sheet summary calculations to tally the total count of a specific dropdown menu item; however, I am using multi-select. I know how to use a standard COUNTIF formula,…","categoryID":322,"dateInserted":"2023-10-13T18:53:53+00:00","dateUpdated":null,"dateLastComment":"2023-10-13T20:17:05+00:00","insertUserID":146191,"insertUser":{"userID":146191,"name":"Tony Fronza","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Tony%20Fronza","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xG908UE9JXI!c4C1Jy0cw2Q!tPis-mqrwhr","dateLastActive":"2023-10-13T19:56:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":165297,"lastUser":{"userID":165297,"name":"gstotts","url":"https:\/\/community.smartsheet.com\/profile\/gstotts","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nSKJ2BAYVP7XH.png","dateLastActive":"2023-10-13T20:44:51+00:00","banned":0,"punished":0,"private":false,"label":"Employee"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":57,"score":null,"hot":3394455658,"url":"https:\/\/community.smartsheet.com\/discussion\/111658\/how-do-i-create-an-countif-statement-to-include-multiple-selections-within-a-cell","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111658\/how-do-i-create-an-countif-statement-to-include-multiple-selections-within-a-cell","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":111658,"commentID":399985,"name":"Re: How do I create an CountIF statement to include multiple selections within a cell?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/399985#Comment_399985","dateInserted":"2023-10-13T20:17:05+00:00","insertUserID":165297,"insertUser":{"userID":165297,"name":"gstotts","url":"https:\/\/community.smartsheet.com\/profile\/gstotts","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nSKJ2BAYVP7XH.png","dateLastActive":"2023-10-13T20:44:51+00:00","banned":0,"punished":0,"private":false,"label":"Employee"}},"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\/AIRIXMJ2U1FC\/countif-statement-inquiry-for-multi-select-fields.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/AIRIXMJ2U1FC\/countif-statement-inquiry-for-multi-select-fields.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/AIRIXMJ2U1FC\/countif-statement-inquiry-for-multi-select-fields.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/AIRIXMJ2U1FC\/countif-statement-inquiry-for-multi-select-fields.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/AIRIXMJ2U1FC\/countif-statement-inquiry-for-multi-select-fields.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/AIRIXMJ2U1FC\/countif-statement-inquiry-for-multi-select-fields.png"},"alt":"COUNTIF Statement Inquiry for Multi-Select Fields.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-13T19:27:20+00:00","dateAnswered":"2023-10-13T19:00:04+00:00","acceptedAnswers":[{"commentID":399956,"body":"

Try using a CONTAINS() statement in your criteria for the COUNTIF:<\/p>

=COUNTIF([Level III Request]:[Level III Request], CONTAINS(\"Solution Video\", @cell))<\/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