Menu
Ask a question Report

Conditional formatting of a cell if another cell is blank [Closed]

Posts
4
Registration date
Wednesday November 6, 2013
Status
Member
Last seen
November 6, 2013
 - - Latest reply: venkat1926
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 - Nov 7, 2013 at 01:42 AM
Excel Ask a question Report
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?



See more 

4 replies

Report
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 787
0
Thank you
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
Report
Posts
4
Registration date
Wednesday November 6, 2013
Status
Member
Last seen
November 6, 2013
0
Thank you
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
Report
Posts
4
Registration date
Wednesday November 6, 2013
Status
Member
Last seen
November 6, 2013
0
Thank you
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"
Report
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 787
0
Thank you
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
Ask a question