Update to Conditional formatting Question

Related to earlier question can you use conditional formatting to look at two columns rather than 1.

In the attached example, column E is being formatted based on the contents of column D and checking dates.
Can this formatting be changed so that if there is no text in the cell in Column D, it looks at the cell in Column C instead.

Thanks

2Columns.xlsx
GordonMassonAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
Revised attachment using LOOKUP......
26975647v2.xlsx
0
 
barry houdiniCommented:
Hello Gordon

Your conditional formatting is only applied when column D is empty......so I'm not sure what you mean by looking at C instead of D. Do you mean that you only want the conditional formatting to be applied if both C and D are empty?

regards, barry
0
 
GordonMassonAuthor Commented:
Hi Barry
In some cases column D may not have a value in it.
If column D DOES have a value then i want to format column E based on those values....as per the demo sheet attached above.
If column D DOES NOT have a value in it then i want to format column E based on the value in column C

Sorry if i am not making this clear but i hope this helps.

Thanks

Gordon
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
ConUladhCommented:
Hi Gordon,

I don't fully understand either.

At the moment the conditional formatting only kicks in if the cell in D is empty.

Are you saying that you only want the conditional formatting to kick in if C is empty as well? If so just add C5 = "" to each rule.

Regards
Stephen
0
 
GordonMassonAuthor Commented:
Sorry guys, I think I am confusing myself now!!

See attached sheet which should hopefully explain what I am trying to achieve… the test sheet I had earlier had columns mixed up in order but this is right

Test-Sheet.xlsx
0
 
barry houdiniCommented:
Hello Gordon, you say:

"If column D DOES have a value then i want to format column E based on those values....as per the demo sheet attached above"

but your conditional formatting isn't using the date in column D.......it's only applied if column D is empty.

Can you just explain what you want to do - preferably without reference to any existing formulas - and which cells should be which colour given that - currently E5, E6 and E8 are red and E13 and E14 are green, should that be different?

regards, barry
0
 
barry houdiniCommented:
Sorry, I posted that last response before I saw yours...

Are you sure F6 should be green. There's a date in H6 so shouldn't F6 remain unformatted? If so try this for green condition

=AND(H1="",OR(G1>TODAY(),AND(G1="",F1>TODAY())))

and this for red

=AND(H1="",OR(AND(G1<TODAY(),ISNUMBER(G1)),AND(G1="",F1<TODAY(),ISNUMBER(F1))))

see attached

regards, barry
26975647.xlsx
0
 
GordonMassonAuthor Commented:
Sorry for the confusion.

I have 3 columns I am interested in and for each row in the sheet:
The first column always has a date in it.
The second column may have a date in it.
The third column may have a date in it.

What I want to achieve is to add a colour fill to the first column but only If the third column does not have a date in it.
If the third column does have a date in it then the first column should retains its white / clear colour fill. (it doesn’t matter what this date is, as long as there is a date in the third column then the first column remains white / clear)

So assuming there is no date in the cell in the third column:
If there is a date in the second column which is AFTER today’s date then the first column should be formatted with green colour fill….. i.e not yet due
If there is a date in the second column which is BEFORE today’s date then the first column should be formatted with red colour fill…..i.e overdue

If there is NOT a date in the second column then the first column should be formatted based on today’s date.
If the date in the first column is AFTER today’s date then the first column should be formatted with green colour fill. ….. i.e not yet due
If the date in the first column is BEFORE today’s date then the first column should be formatted with red colour fill. …..i.e overdue

I hope this helps explain things. Sorry if its not clear.
0
 
GordonMassonAuthor Commented:
Yes that looks right thanks barry...just checking in more detail now and of course you were right about F6.
0
 
barry houdiniCommented:
Actually there's a simpler way......

If you use a LOOKUP formula that will return G1 if that has a date but F1 otherwise, so your green condition can be

=AND(H1="",LOOKUP(10^10,F1:G1)>TODAY())

and red identical except with > changed to <

that should give the same results as my previous suggestion

regards, barry
0
 
GordonMassonAuthor Commented:
Thanks for your help Barry

Cheers
Gordon
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.