Highlighting a range of cells based on 2 conditions

[MariaFarag]

Hello,

I am trying to use the formula AND($A5="Showroom Order", $BG5<>'') because i want the range of cells from A5 to AI to be highlighted in light green when column A is "showroom order" AND what's under column BG is no longer blank (it might be N/A or it might be a manually inserted date). the N/A in column BG will appear based on a formula - so column BG is formulated, and also has a drop down list.

but the formula isn't working. even if I put NOT(BLANK($BG5)) instead of $BG5<>'' in the above AND formula, it doesn't work

can someone help please with suggestions?

 

[mart37]

AND($A5="Showroom Order", NOT(ISBLANK($BG5)))

 

[Fluff]

You have the wrong type of quotes at the end of your formula, try

=AND($A5="Showroom Order", $BG5<>"")

@mart37
BG5 has a formula so ISBLANK will always return false

 

[MariaFarag]

Thank you Mart37 and Fluff, yes correct BG5 has a formula.

I am now facing another issue with the below formula. I'm not familiar with which formulas for conditional formatting don't work when a cell is formulated, and none of the online solutions I'm getting is solving the problems I'm facing with the formulas.

So what I want is for cells BJ to BT to be highlighted in green if:
1. The scope of work is either "delivery and installation" or "Installation Only"
then check:
2. is type of installation full and the installation date 1 cell before today?
3. is the type of installation partial and the final installation date before today?

Can someone help please?

Note: I know the formula in the below image is in a cell and it should be in the formula bar of conditional formatting. it actually is in the original sheet, and the details in the respective cells are available (ie. BO says Full, and the installation date 1 shows Feb.5 as a date), so the green highlight should appear but it's not

Thank you in advance

 

[mart37]

$BT5="Installation only"

 

[MariaFarag]

oh thank you! even thought i looked at the formula several times, but didn't realize i missed the cell itself.

what about the below if you don't mind please:

I'm trying to do the conditional formatting for this as follows:

=OR($BH5="N/A", "Already Returned") --> to highlight these cells (BH5 to BH7) in green

BH5, BH6, BH7 are the cells which have N/A in the below image

they are formulated - so does the "OR" formula not work also in conditional formatting when a cell is formulated?

 

[mart37]

=OR($BH5="N/A", $BH5="Already Returned")

 

[MariaFarag]

=OR($BH5="N/A", $BH5="Already Returned")

Thank you

 

[MariaFarag]

hi Mart37,

I still have 2 formulas which I need to add to the list, but I'm not sure I'm close to figuring it out and the deadline for me to finish is tomorrow. Can you please help?

I will share here the first formula and then the other after this one's done. I am trying to apply the below condition:

1) Highlight cells A5:AI13 when the below conditions are met:
1.1) cell A is either a "Project" or a "Repair"
AND
1.2) Cell BK is either N/A or less than today's date OR BM is less than today's date
AND
1.3) Cell BP is either N/A or less than today's date OR BR is less than today's date
AND
1.4) CR is "paid"

I have tried to apply the IF formula but felt it will be complicated. Should it be the IF formula or something else?

Thank you in advance

Maria

 

[mart37]

You need a combination of AND and OR. (like you describes your problem)
=AND(OR($A5="project";$A5="repair");OR($BK5="N/A";$BK5<TODAY();$BM5<TODAY());OR($BP5="N/A";$BP5<TODAY();$BR5<TODAY());$CR5="paid")