?
Solved

Update to Conditional formatting Question

Posted on 2011-04-25
11
Medium Priority
?
205 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:GordonMasson
  • 5
  • 5
11 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 35460607
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
 

Author Comment

by:GordonMasson
ID: 35464993
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
 
LVL 8

Expert Comment

by:ConUladh
ID: 35465449
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:GordonMasson
ID: 35465644
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 35465668
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 35465782
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
 

Author Comment

by:GordonMasson
ID: 35465788
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
 

Author Comment

by:GordonMasson
ID: 35465806
Yes that looks right thanks barry...just checking in more detail now and of course you were right about F6.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35465834
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 35465847
Revised attachment using LOOKUP......
26975647v2.xlsx
0
 

Author Closing Comment

by:GordonMasson
ID: 35510652
Thanks for your help Barry

Cheers
Gordon
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question