Flagging an empty in a cell, if another cell contains a value

Occasional Visitor

Two part question:

 

FIRST

 

I'm trying to highlight red, via conditional formatting, cases in which a survey respondent answered "other" in a multiple choice question, but didn't add an answer to the subsequently required "please specify".

 

In the attached, you'll see formulas in column C - L to mark the cell with a "1" if a respondent selected the corresponding answer.

 

In column M, I tried the following conditional formatting: =AND(L4=1,M4=""), which works when I manually enter 1 into column L (see cell L20) but does not work when 1 is populated from a formula (see cell L21).

 

SECOND

 

I'd like to create a separate column that identifies (TRUE OR FALSE) when this occurs across 75 survey questions. Is it possible to simply create a formula that flags when a cell is highlighted red as a result of conditional formatting?

 

Screen Shot 2022-07-31 at 2.12.50 PM.png

1 Reply

@dlthomas_2000

FIRST 

Can you make the formulas in columns C to L return the number 1 (without quotes) instead of the text value "1"?

If so, your conditional formatting should work.

If it is not possible, change the conditional formatting formula to =AND(L4="1",M4="")

 

SECOND

Use the same formula that you use for conditional formatting. Or am I missing something?