If blank fill in with default name and if none blank copy next column in

Options

I will need to copy from name A to column B and if the blank column(highlighted with yellow) will have default name which depending on site location below

Pls help since I am new to Smartsheet

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    You need to make sure the parenthesis and quotation marks are in the right place. Every opening parenthesis must have a closing one. You added an opening one but no closing one. You also added a quotation mark (and no closing one).

    The part you added is in bold here

    =IF(ISBLANK([Name A]@row), IF([Org Code]@row = "US2", "Bengy", IF([Org Code]@row = "KO3", "Freddie", "IF([Org Code]@row="CN2","Bengy")), [Name A]@row)

    What you need to add is in bold here

    =IF(ISBLANK([Name A]@row), IF([Org Code]@row = "US2", "Bengy", IF([Org Code]@row = "KO3", "Freddie", IF([Org Code]@row="CN2","Bengy"))), [Name A]@row)

    You can use the process here: https://community.smartsheet.com/discussion/comment/410008#Comment_410008

    to spot which parenthesis is not paired.

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    You can use an IF function again - this time combined with ISBLANK.

    This is how to use ISBLANK

    In your case, if false you would return [Name A]@row, like this:

    =IF(ISBLANK([Name A]@row), "formula for default names", [Name A]@row)

    If Name A is not blank you would have another IF function in place of the text formula for default names. This will look at the Org Codes. I assume you have included the Org Codes in your sheet with Name A like this

    This is how to use nested IF

    Here is an example to get you started

    =IF([Org Code]@row = "US2", "Bengy","not Bengy")

    This will return Benjgy when the Org Code is US2 and not Bengy when it is anything else. You can replace not Bengy with another IF function like this

    =IF([Org Code]@row = "US2", "Bengy", IF([Org Code]@row = "KO3", "Freddie","next IF here"))

    This means if the Org Code is US2 then the value is Bengy. If not, another If formula is evaluated. This time if the org code is KO3 then the value is Freddie. If not the text next IF here is returned. You can replace "next IF here" with another IF function. And repeat until you have your full formula.

    You can paste that into the first formula like this:

    =IF(ISBLANK([Name A]@row), IF([Org Code]@row = "US2", "Bengy", IF([Org Code]@row = "KO3", "Freddie", "next IF here")), [Name A]@row)


  • HZAR
    HZAR ✭✭✭
    Options

    Hi KPH

    I have written as per your guidance but it gv me incorrect argument for below formula. Is ther anything that I miss out? Pls help to advise. Thanks

    =IF(ISBLANK([Name A]@row), IF([Org Code]@row = "US2", "Bengy", IF([Org Code]@row = "KO3", "Freddie", "IF([Org Code]@row="CN2","Bengy")), [Name A]@row)

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    You need to make sure the parenthesis and quotation marks are in the right place. Every opening parenthesis must have a closing one. You added an opening one but no closing one. You also added a quotation mark (and no closing one).

    The part you added is in bold here

    =IF(ISBLANK([Name A]@row), IF([Org Code]@row = "US2", "Bengy", IF([Org Code]@row = "KO3", "Freddie", "IF([Org Code]@row="CN2","Bengy")), [Name A]@row)

    What you need to add is in bold here

    =IF(ISBLANK([Name A]@row), IF([Org Code]@row = "US2", "Bengy", IF([Org Code]@row = "KO3", "Freddie", IF([Org Code]@row="CN2","Bengy"))), [Name A]@row)

    You can use the process here: https://community.smartsheet.com/discussion/comment/410008#Comment_410008

    to spot which parenthesis is not paired.

  • HZAR
    HZAR ✭✭✭
    Options

    HI KPH


    Thanks you so much for you guidance.. you're such an awesome teacher :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!