conditional formatting of a cell if another cell is blankClosed

Question

Hello, All
i am challenged in resolving the following problem I have and am hopefull that someone can help

in Column E I have specific text "tier 2" or "Tier 1" I would like to format the color column L if Column E = Tier 2 and the value in the corresponding cell in column L is blank. 

 below a example 
Col E = tier 2 and Col L is blank then the cell for Col L must turn red 
Col E = tier 1 and Col L is blank then stay unformatted. 

any suggestions would be great and thank you in advance.

Hi All
i have entered the below conditional format (=E24="Indirect: 2-Tier") into cells L24 ~ 30 to test.

L24 is blank and E24= "Indirect: 2-Tier" and with the below formula the formatting works fine.

however in a cell L26 I do have a value entered and E26 =Indirect: 2-Tier and the formatting still works, I would like the cell to remain unchanged.

any suggestion on a formula that will help to overcome this?



Configuration: Mac OS X (10.9.0) / Chrome 30.0.1599.101

venkat1926 · Answer

I woner whether conditiona formattsing cell and critiera cell can eb same


select M2 clilck home-conditoan formatting-manage rules-new rule-use a formula tol determibe.........-, below the snmall window tregardixng formula type the formula
=and(E2="tier 2",L2="")
and then do whatever formal lyou want. 
\
copy M2 down pastespecial -format

Ant104 · Answer

Brilliant thank you venkat.........

if I may as another question, I also want to do a conditional format of a cell based on a date.

example Cell B8 has a date in it of lets say 20 Nov 2013

i would like to format the cell as following
1) Green if the date is =  or older than todays date IF an other specific cell (F8) has "Booked"  in it.

2) then if the date of the cell is in the past (and not having the text of "Booked" in F8) the the cell must turn red

3) if the date in B8 is within 10 days of today date then cell must turn yellow
4) if the date in cell B8 is  between 11 ~ 20 days then turn orange


if you could help it would be great.

thanks Ant

Ant104 · Answer

one more question I would also like the cell B8 to turn blue if the date in that cell is due within the next 30 days and cell G8 does not have a specific text in it lets say  "Agreed"

or E8 "Tier 2"

venkat1926 · Answer

for the first if F2>=toay() and F8 is "booked" then conditionla formatting(CF) formula is 


=AND(F2>=TODAY(),$F$8="booked")

select F2 anbd then type the fo;rmla in CF
format as you like

I wonder why you are haveing in columnF boit date and string. in excel lit is better to use a column for a particular variable unless it is absolutely necessary

based aon above formula lcan you try other conditions?

any problem revert to newsgroup

Conditional formatting of a cell if another cell is blank

4 replies

Subscribe To Our Newsletter!