Formula help! If cell is not blank, return value from another cell

liammac1988
liammac1988 ✭
inFormulas and Functions

I'm having trouble building a formula that works for the below requirement (see screenshot for reference).

If the cell in column 'INT / DOM / TEMP / NG' is NOT BLANK, then I need the column 'TITLE (Formula)' to pull through the value from a specific cell ('A1' in Excel terms, or 'TITLE / Security Title' in the example shown in the screenshot - the one in blue and yellow).

This is needed because I want to create several duplicates of this sheet, each one for a different 'TITLE / Security Title', and then create a Report that pulls all of the sheets together, with the rows able to be grouped by 'TITLE (Formula)' but ONLY if those rows contain anything in the 'INT / DOM / TEMP / NG' column.

I hope this makes some sense - can anyone help me with the formula?

Screenshot 2023-10-28 at 7.27.34 pm.png


· Share on Facebook Share on Twitter

Best Answer

  • KPH
    KPH ✭✭✭✭
    Answer ✓

    Hi

    I believe this formula, entered in the TITLE (Formula) column would work for you:

    =IF(ISBLANK([INT / DOM / TEMP / NG]@row), "", TITLE$1)


    It creates this:

    image.png


    You start with an IF

    For the logical expression you use ISBLANK

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

    If that is true it puts in "" (ie nothing)

    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

    · Share on Facebook Share on Twitter

Answers

  • KPH
    KPH ✭✭✭✭
    Answer ✓

    Hi

    I believe this formula, entered in the TITLE (Formula) column would work for you:

    =IF(ISBLANK([INT / DOM / TEMP / NG]@row), "", TITLE$1)


    It creates this:

    image.png


    You start with an IF

    For the logical expression you use ISBLANK

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

    If that is true it puts in "" (ie nothing)

    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

    · Share on Facebook Share on Twitter
  • liammac1988
    liammac1988 ✭

    AMAZING thank you!

    · Share on Facebook Share on Twitter
  • KPH
    KPH ✭✭✭✭

    My pleasure!

    · Share on Facebook Share on Twitter

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 the公式手册模板!
That formula looks good. It needs to be in the email cell - where you want the email to appear. The yellow ones here:<\/p>
\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Can you try the cross reference again? Double click on the formula and put your cursor onto the reference like this:<\/p>

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

Then click \"Edit Reference\" on the pop up.<\/p>

Make sure you have highlighted the two columns you need, that the email address is in the second one, and your \"Sheet reference name\" looks exactly <\/strong>like this <\/p>

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

🤞<\/span><\/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"}]},{"discussionID":112325,"type":"question","name":"Help with #Unparseable Formulas","excerpt":"Hi Everyone! I am a new Smartsheet user and am having some issues creating formulas that are based off of multiple columns where those columns are set up as drop downs with multiple selections. My Formula: =SUMIFS({Deliverables}, {Work Type} HAS (@cell, Reprint), {Fiscal Quarter}, HAS (@cell, \"Q1\"), {Fiscal Year}, HAS…","snippet":"Hi Everyone! I am a new Smartsheet user and am having some issues creating formulas that are based off of multiple columns where those columns are set up as drop downs with…","categoryID":322,"dateInserted":"2023-10-30T14:18:55+00:00","dateUpdated":null,"dateLastComment":"2023-10-30T15:33:41+00:00","insertUserID":169246,"insertUser":{"userID":169246,"name":"jimhmeyers","url":"https:\/\/community.smartsheet.com\/profile\/jimhmeyers","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-30T15:51:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":169246,"lastUser":{"userID":169246,"name":"jimhmeyers","url":"https:\/\/community.smartsheet.com\/profile\/jimhmeyers","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-30T15:51:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":57,"score":null,"hot":3397359156,"url":"https:\/\/community.smartsheet.com\/discussion\/112325\/help-with-unparseable-formulas","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112325\/help-with-unparseable-formulas","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":112325,"commentID":402230,"name":"Re: Help with #Unparseable Formulas","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402230#Comment_402230","dateInserted":"2023-10-30T15:33:41+00:00","insertUserID":169246,"insertUser":{"userID":169246,"name":"jimhmeyers","url":"https:\/\/community.smartsheet.com\/profile\/jimhmeyers","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-30T15:51: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-30T15:03:11+00:00","dateAnswered":"2023-10-30T14:53:42+00:00","acceptedAnswers":[{"commentID":402218,"body":"

I don't know if the extra spaces between HAS and your open parens would cause an issue, but you are also missing quotes around Reprint.<\/p>

=SUMIFS({Deliverables}, {Work Type}, HAS(@cell, \"Reprint\"), {Fiscal Quarter}, HAS(@cell, \"Q1\"), {Fiscal Year}, HAS(@cell, \"FY24\"))<\/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"}]},{"discussionID":112320,"type":"question","name":"return date which is next higher one","excerpt":"Hi, I'm looking for a formula to check the \"End date\" and return the next higher from date from the reference sheet. e.g. End Date is May 7, the result should be Pre-confirmed 90 LT=May 8 or End Date is June 16, the result should be Pre-confirmed 90 LT=June 26 I have no idea which formula could work. Thanks for your help!","snippet":"Hi, I'm looking for a formula to check the \"End date\" and return the next higher from date from the reference sheet. e.g. End Date is May 7, the result should be Pre-confirmed 90…","categoryID":322,"dateInserted":"2023-10-30T08:05:20+00:00","dateUpdated":null,"dateLastComment":"2023-10-30T12:45:44+00:00","insertUserID":131273,"insertUser":{"userID":131273,"name":"Christine Menke","url":"https:\/\/community.smartsheet.com\/profile\/Christine%20Menke","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!!wxtct5RkPno!3WtdPxB0jLw!Ul_V8Bw57zI","dateLastActive":"2023-10-30T12:53:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-30T17:19:41+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":36,"score":null,"hot":3397326064,"url":"https:\/\/community.smartsheet.com\/discussion\/112320\/return-date-which-is-next-higher-one","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112320\/return-date-which-is-next-higher-one","format":"Rich","lastPost":{"discussionID":112320,"commentID":402209,"name":"Re: return date which is next higher one","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402209#Comment_402209","dateInserted":"2023-10-30T12:45:44+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-30T17:19:41+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\/19AUMWBF6VWT\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/19AUMWBF6VWT\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/19AUMWBF6VWT\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/19AUMWBF6VWT\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/19AUMWBF6VWT\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/19AUMWBF6VWT\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-31T05:02:35+00:00","dateAnswered":"2023-10-30T10:26:53+00:00","acceptedAnswers":[{"commentID":402191,"body":"

Hi @Christine Menke<\/a>,<\/p>

A formula like this should work:<\/p>

=INDEX([Pre-confirmed 90 LT]:[Pre-confirmed 90 LT], MATCH([End Date]@row, [Pre-confirmed 90 LT]:[Pre-confirmed 90 LT]) + 1)<\/p>

Some example data:<\/p>

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

Hope this helps, but if you've any problems\/questions, then just post! 🙂<\/span><\/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

  • Struggling with Formula. Not sure if using correct one.

    I have 2 sheets. We'll call Sheet A "Roster" and Sheet B "Grading Sheet" Sheet A has columns for "Employee Number" and "Email Address" What I am trying to do is on Sheet B, have a column in which you can enter the employee number, it reference Sheet A and then it will automatically enter the email address associated with…

  • Help with #Unparseable Formulas

    Hi Everyone! I am a new Smartsheet user and am having some issues creating formulas that are based off of multiple columns where those columns are set up as drop downs with multiple selections. My Formula: =SUMIFS({Deliverables}, {Work Type} HAS (@cell, Reprint), {Fiscal Quarter}, HAS (@cell, "Q1"), {Fiscal Year}, HAS…

  • return date which is next higher one

    Hi, I'm looking for a formula to check the "End date" and return the next higher from date from the reference sheet. e.g. End Date is May 7, the result should be Pre-confirmed 90 LT=May 8 or End Date is June 16, the result should be Pre-confirmed 90 LT=June 26 I have no idea which formula could work. Thanks for your help!