Formula to check the cell for 4-5 text strings

Mindfull · 07/06/20

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 🙂

Stefan · 07/07/20

Hi Jain,

start easy with the ISBLANK only:

=IF(ISBLANK([Requested By]@row), "empty", "not empty")

Then add your working formula from above in place of "not empty". The logic is: First check if the cell is empty and display some result for that and if the cell is not empty use the other formula.

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

Hope this helps

Stefan

Paul Newcome · 07/06/20

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 · 07/06/20

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

Mindfull · 07/06/20

https://community.smartsheet.com/discussion/comment/255615#Comment_255615

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 · 07/06/20

https://community.smartsheet.com/discussion/comment/255617#Comment_255617

Still of no help Stefan,

Prajna

Mindfull · 07/06/20

Paul can you help me writing the exact formula?

Paul Newcome · 07/06/20

What is the name of the column you are referencing?

Stefan · 07/06/20

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

Mindfull · 07/07/20

@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 · 07/07/20

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

Mindfull · 07/07/20

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

Thanks

Stefan · 07/07/20

Jain,

great :-)

You can capture blank cells easily with the ISBLANK function:

https://help.smartsheet.com/function/isblank

Hope this helps

Stefan

Mindfull · 07/07/20

@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. :(

Stefan · 07/07/20

Hi Jain,

start easy with the ISBLANK only:

=IF(ISBLANK([Requested By]@row), "empty", "not empty")

Then add your working formula from above in place of "not empty". The logic is: First check if the cell is empty and display some result for that and if the cell is not empty use the other formula.

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

Hope this helps

Stefan

Mindfull · 07/07/20

@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 · 07/07/20

Hi Jain,

glad I could help !

Have a great day

Stefan

Formula to check the cell for 4-5 text strings

Best Answer

Answers

Help Article Resources

Categories