Formula to check the cell for 4-5 text strings

Options

I have a condition where in the formula should check the text in CELL A and return value based on the assigned values and should leave the formula cell BLANK if none of the condition is satisfied.

I tried it through spreadsheet and the formula is:

IF(OR(A1= "Name A", A1="Name B",A1="Name C",A1="Name D",A1="Name E",A1="Name F"),"XYZ","ABC").

when I am converting this to Smartsheet formula, it is coming as #UNPARSEABLE:

IF(OR(CELL1@row= “NAME A”, CELL1@row = ”NAME B”, CELL1@row=”NAME C”, CELL1@row=”NAME C”, CELL1@row =”NAME D”, CELL1@row=”NAME E”,"XYZ”,"ABC").

I would appreciate a quick response 🙂

Tags:

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You would need to change "CELL1" to the actual column name. It also looks like you are using "smart quotes" which are the slanted quotes that show open vs closed. You will need to remove those and retype them within Smartsheet so that they are replaced with the correct type of quotes.

  • Stefan
    Stefan ✭✭✭✭✭✭
    Options

    Hi Jain,

    creating formulas in Smartsheet instead of importing excel formulas is the better, because Smartsheet has similar but not the same and not all Excel formulas.

    You missed to close the brackets for the OR part.

    ...CELL1@row=”NAME E”),"XYZ”,...

    Hope this helps

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Mindfull
    Mindfull ✭✭✭✭✭
    Options

    No I am not using slanted quotes still it no giving me result and instead of CELL1 I am using actual reference, here it is just for giving an example.

  • Mindfull
    Mindfull ✭✭✭✭✭
    Options

    Paul can you help me writing the exact formula?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    What is the name of the column you are referencing?

  • Stefan
    Stefan ✭✭✭✭✭✭
    Options

    Jain,

    in my example

    ...CELL1@row=”NAME E”),"XYZ”,...

    CELL1 needs to be the exact name of the column. Unlike Excel columns in Smartsheet have names used to reference them in formulas.

    Hope this helps

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Mindfull
    Mindfull ✭✭✭✭✭
    Options

    @Paul here is the exact formula I have created:

    IF(OR([Requested By]@row= “NAME A”, [Requested By]@row = ”NAME B”, [Requested By]@row=”NAME C”, [Requested By@row]=”NAME C”, [Requested By]@row =”NAME D”, [Requested By]@row=”NAME E”,"XYZ”,"ABC"))

    Thanks

    Prajna

  • Stefan
    Stefan ✭✭✭✭✭✭
    Options

    Jain,

    do you eventually build the formula outside of Smartsheet and copy&paste it into the cell?

    I got the formula below working with no problem after I manually rebuilt your formula in Smartsheet. While doing so I noticed again, that the closing bracket for the OR function was not in the right place and that several quotes where different to those typed into a Smartsheet cell directly.

    =IF(OR([Requested By]@row = "NAME A", [Requested By]@row = "NAME B", [Requested By]@row = "NAME C"), "XYZ", "ABC")

    Hope this helps

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Mindfull
    Mindfull ✭✭✭✭✭
    Options

    @Stefan, it is working now but still it is not considering the situation when the [Requested by] is blank.

    Thanks

  • Stefan
    Stefan ✭✭✭✭✭✭
    Options

    Jain,

    great :-)

    You can capture blank cells easily with the ISBLANK function:

    Hope this helps

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Mindfull
    Mindfull ✭✭✭✭✭
    Options

    @Stefan I tried it this way:

    =IF(ISBLANK([Requested By]@row, "", OR([Requested By]@row = "NAME A", [Requested By]@row = "NAME B", [Requested By]@row = "NAME C"), "XYZ", "ABC"))

    it is throwing an #INCORRECT ARGUMENT ERROR. :(

  • Mindfull
    Mindfull ✭✭✭✭✭
    Options

    @Stefan I tried as per your suggestion it is working now; instead of empty i just used ""

    =IF(ISBLANK([Requested By]@row), "", IF(OR([Requested By]@row = "NAME A", [Requested By]@row = "NAME B", [Requested By]@row = "NAME C"), "XYZ", "ABC"))

    Thanks

    Prajna

  • Stefan
    Stefan ✭✭✭✭✭✭
    Options

    Hi Jain,

    glad I could help !

    Have a great day

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!