Search code examples
excelexcel-formulaformulaexcel-2010conditional-formatting

IF AND and conditional formatting question

I am trying to find the right formula for the below situation. Not sure if conditional formatting is the answer or a formula or a combination. I have reviewed guidance for how to use IF and IFS as well as how to do conditional formatting based on another cells value but nothing has worked.

I have a range of cells (M3:M400) that are either blank or have text. If they have text I don't want anything to change but if they are blank I want an outcome (cell colour or text) I then have a other range of cells (D3:D400) that all have a different text, varying between four different words. I only want one of the words to be identidied. For the sake of this I only want apple to be identified.

Basically I want the following to happen - if M3:M400 is blank AND D3:D400 says apple (only) I want the M cell to say a specified text such as "apples only" or to be a certain colour using conditional formatting. I made a bit of formula that obviously doesn't work for reference

=IF(AND(D3:D402="APPLE",(M3:M402="")),"ONLY APPLES","")

No idea how this is supposed to be arranged.

Edit: The advice was really helpful, I've been able to make a working formula with all the contributions. Still haven't been able to make conditional formatting work but advice has been great for this either way.

Formula as follows- =IF(AND(IS BLANK($M3),D3="Apples"),"True","False")

Solution

  • You can base yourself on the formula =AND(ISBLANK($M3);D3="Apple") as in the following screenshot (where some obsolete columns are hidden):

    enter image description here

    In cell "N3", you see the result of the mentioned formula, but obviously, you can't enter this formula in cell "M3" itself as the presence of this formula turns it to be non-blank, hence the usage of the half-absolute cell reference "$M3".