Having trouble with IF "CONTAINS" function

LeAndre P

Hello,

I have a column with a dropdown list that allows users to select Yes or No to satisfy the requirements of a question. If the user choses “Yes” they are then required to provide details to explain their answer. To eliminate blank cells on the sheet where the answers are recorded, I used the following formula to return the result:  =IF(CONTAINS("No", [Site related delays:]@row), "N/A", " ")

The formula works if the user choses “No” however, if the user chooses ‘Yes” and provides details as required the cell isn’t return the user input data(text) it is only showing blank. How do I correct this?

Formula:

=IF(CONTAINS("No", [Site related delays:]@row), "N/A", " ")

 

Thank you,

Jason Duryea

If [Site related delays:]@row) is a Yes/No field (i.e. drop down), then you shouldn’t need to use the CONTAINS function.

=IF([Site related delays:]@row)=“No”, “N/A”, IF([Site related delays:]@row)=“Yes”, “Yes result”, “”)

LeAndre P

Hi Jason I'm getting #UNPARSEABLE with this formula.

Paul Newcome

The reason for the Unparseable error is because of the type of quotes used. The slanted quotes are "Smart Quotes" and (ironically enough) Smartsheet doesn't recognize them. You have to use the quotes that are straight up and down such as the ones you see here in this post (typed directly in instead of a copy/paste), typed directly into the sheet itself, or typed into a text editor such as Notepad (not Word).

Really though all you should need to do is replace the "blank" output in your existing formula with a direct cell reference to the cell containing the user input.

=IF(CONTAINS("No", [Site related delays:]@row), "N/A", [User Input Column]@row)

I do however agree that if the yes/no column is a dropdown with only those two options as a possibility then you should not need the CONTAINS function.

=IF([Site related delays:]@row = "No", "N/A", [User Input Column]@row)

LeAndre P

Hi Paul,

Thank you for the clarifying explanation it really helped however, I am now getting #CIRCLE REFERENCE error. I don’t think I mentioned the formula being placed in the cell/column hosting the user entry. A bit more background to help figure this out.

There is a bit of logic associated with the source column/cell, where a text box becomes available if the user selects “Yes” as the answer to the parent question. Form users have the option to select Yes/No from a dropdown nested within a question named [Site related delays:]. If the user selects “NO” I would like to return N/A in the corresponding cell located in the [Please explain site constraint:] column. Likewise, if the user selects “Yes” a text box is revealed for the use to enter a detailed explanation of their constraint. It is this result that I would like to have returned in the corresponding cell instead of the aforementioned N/A. Is this possible?

 

Thank you,

Paul Newcome

You would need to either have the formula in a different column or not use the conditional logic in the form and set the field's default value as "N/A".

LeAndre P

Thanks Paul..

Paul Newcome

Happy to help. 👍️