Formula to check if a cell contains "No" or isblank

Christina C

This is probably so simple but I am struggling with my if and isblank, or maybe I don't need isblank and need just "" empty quotes. The Column title is "Added to CRM" and it can contain Yes, No, Revised or blank.

I would like to check a cell and if it contains either the word "No" or is blank return a phrase like "Open". If it contains any other word I don't care.

Help! Thank you.

Paul Newcome

Try this:

=IF(OR([Added to CRM]@row = "No", [Added to CRM]@row = ""), "Open")

Paul Newcome

Try this:

=IF(OR([Added to CRM]@row = "No", [Added to CRM]@row = ""), "Open")

Christina C

Thank you! That worked. I was using IFBlank in the wrong way all I needed was a good old OR. Thanks again!

Paul Newcome

Happy to help. 👍️

K.J

Hey @Paul Newcome, thank you for responding to this. I'd like to do the same however my original formula also checks for double up conflicts throughout the list. I use the red flag to show a conflict.

My original code snippet works for conflicts but when the cell is blank it also flags it as a conflict which makes sense.

=IF(COUNTIFS([vMix Metric]:[vMix Metric], @cell = [vMix Metric]@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, 1)

I was attempting to add a ISBLANK in the hope I could clear up the blank cell conflict, but getting an error of INCORRECT ARGUMENT SET.

=IF(ISBLANK([vMix Metric]:[vMix Metric]), 0, IF(COUNTIFS([vMix Metric]:[vMix Metric], @cell = [vMix Metric]@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, 1))

I would love some advice if possible! Thanks in advance.

Paul Newcome

@K.J Try this:

=IF([vMix Metric]@row <> "", IF(COUNTIFS([vMix Metric]:[vMix Metric], @cell = [vMix Metric]@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, 1))

K.J

https://community.smartsheet.com/discussion/comment/389441#Comment_389441

That's great, Paul, working well! Thank you very much. I've been stuck for a few weeks just tweaking the previous snippets.

There is an anomaly with one line where the dates don't overlap and it's being flagged, but it's only the one row that's flagging and not both which is how it worked in testing with your snippet. So, I'm not fussed about that.

Paul Newcome

Glad it is mostly working for you, but even one issue means we need to check it out.

What happens if you apply a filter to the sheet to only show rows for that particular [vMix Metric]? Are you able to provide a screenshot of the results?

K.J

Thanks for following up, @Paul Newcome. This filter is for licences, so I can see who has asked for what. The colours represent a single licence available from a dropdown cell (we only have 4 licences so all are shown here), we can see that the last yellow is showing a conflict, however as we can see below, the yellow dates don't overlap.

Paul Newcome

What happens if you apply a filter to only show rows that are flagged?

K.J

Brilliant! Thank you, I had a line not coming through properly with the other filter. Working perfectly :)

Paul Newcome

Happy to help. 👍️