Excel Conditional Formatting - Grey out cell when criteria not met.

[nhsmedic]

Hello Everyone.

I am making a data collection form.

Within the form, one column has cells - where there are multiple inputs.

I want the cell under SALT assessment to stay greyed out - and only become visible if either "chewing problem", or "swallowing problem" is selected in a cell from the AD column

There are many input variables in the cell - so I have included AD4 - which says Drooling, Bloating, Swallowing Problem. As "swallowing problem" is in this cell - I want the cell to not be greyed out.

In contrast, if the cell was to contain anything other than "chewing problem", or "swallowing problem" - I want it to remain grey.


I tried =NOT(OR($AC3="Chewing problem", $AC3="Swallowing problem")). However this only works if the only value in the cell is that one of the two. The moment another option is added, eg so the cell reads "Swallowing problem, drooling" - the cell becomes grey again. As witnessed below

I will appreciate any help.

Kind Regards

 

[Fluff]

Hi & welcome to MrExcel.
How about

=NOT(OR(ISNUMBER(SEARCH("Chewing problem",$AC3)),ISNUMBER(SEARCH("Swallowing problem",$AC3))))

 

[JamesCanale]

Try something like this:

=OR(NOT(ISERR(SEARCH("chew",K11))),NOT(ISERR(SEARCH("swallow",K11))))

 

[Fluff]

A slightly simpler option to post#2

=AND(ISERROR(SEARCH("Chewing problem",$AC3)),ISERROR(SEARCH("Swallowing problem",$AC3)))

 

[nhsmedic]

@JamesCanale I tried this one, wihch gave the following. It highlighted the cells contained chew / swallowing - however I want the cells not containing those to be highlighted.

=OR(NOT(ISERR(SEARCH("chew",$AC3))),NOT(ISERR(SEARCH("swallow",$AC3))))

@Fluff - =AND(ISERROR(SEARCH("Chewing problem",$AC3)),ISERROR(SEARCH("Swallowing problem",$AC3))).

This one worked perfectly

Thank you for your help sir. If I have any more questions, I will let you know - however I will use the above formula and adapt it to the other variables.

Regards

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[nhsmedic]

@Fluff

Sorry. I forgot to mention - I also want the option to remain Grey - except for when cell AD4 for Speech difficulties says Yes. - as Having speech difficulties trigges SALT refferal.

I tried the following:

=AND(ISERROR(SEARCH("Chewing problem",$AC3)),ISERROR(SEARCH("Swallowing problem",$AC3),ISERROR(SEARCH("Yes",$AD3)))

However - when trying it says 'You've entered too many arguments'

 

[nhsmedic]

@Fluff

If it helps explaining - someone may have have a Swallowing / Chewing problem - however - may have a speech problem. In that case I would also want SALT referral to be triggered. As in the picture below

Kind Regards

 

[Fluff]

Will AD only have "Yes" or could it have "Yes" along with other text?

 

[nhsmedic]

Will AD only have "Yes" or could it have "Yes" along with other text?

Currently it is Just Yes - or No.

However - it may have Yes - dysarthria in the future.

If the formula is different - please include ones for both.

Regatds