Conditional formatting using a date from another cell, and using 'ifs'

[BeckyMaclean]

I would like to use a conditional formatting formula, to turn certain cells in column E, for example, red, if the date in column D is older than 2 weeks. I would only like the cells in column E to go red, if they say 'yes' or 'waiting on client.'

Can someone help with a formula to do this?

To explain why I need it, so it makes sense:

In column D I have the date a brochure was sent out, and in column E, I have 'venue visit booked' with a list of either 'yes', 'no' or 'waiting on client.' If it has been 2 weeks since the brochure was sent out and it still says 'no' or 'waiting on client' I want the cell to go red, so that I can do something about it.

Can anyone help?

Also, is there a way to do conditional formatting, so that you have one formula, but then override it, if another formula is true?

E.g. column A is purple if it contains less than 10, but if column C is green, then column A goes blue. (probably a bad example!)

[etaf]

you could use
in conditional format
add a formula
and use
=AND( OR(E2="yes",E2="waiting for client"),D2<TODAY()-14)

[etaf]

duplicated

[etaf]

Also, is there a way to do conditional formatting, so that you have one formula, but then override it, if another formula is true?

E.g. column A is purple if it contains less than 10, but if column C is green, then column A goes blue. (probably a bad example!)

you can add a number of different formulas in conditional format and also use a stop if true
so that the order you put in can override the other formulas when true - as the conditional format stops

also my attachment was not added - so added now

[BB1972]

Yeah, the server was misbehaving while I was trying to post an answer.

Becky, welcome to the forum.

[BeckyMaclean]

Thank you for your help etaf!

I have used the formula, but it doesn't seem to be working. Am I doing something really stupid and wrong? Any suggestions? :S

[etaf]

can you post a sample spreadsheet

edit the formula again
sometimes cond format puts "" on the wrong places if an = is not used

[BeckyMaclean]

you can add a number of different formulas in conditional format and also use a stop if true
so that the order you put in can override the other formulas when true - as the conditional format stops

also my attachment was not added - so added now

Thanks for the spreadsheet, and your help! I edited the formula just so it matches the columns I'm using, but it still doesn't work. The 'yes', 'no' and 'waiting on client' are in a list and data validation drop down box, does this affect it?

Yeah, the server was misbehaving while I was trying to post an answer.

Becky, welcome to the forum.

Thank you! I should really have said hi before just going right in and posting!

[etaf]

The 'yes', 'no' and 'waiting on client' are in a list and data validation drop down box, does this affect it?

No
But the formula does not have any $ in and the apply to area will

need to have a look at your spreadsheet now really

[BeckyMaclean]

Sample spreadsheet.xlsx

Hopefully that upload worked? And hope the spreadsheet works too. I am so confused :s

If that doesn't work, I can upload my whole spreadsheet.

[etaf]

you seem to have a different formula
=AND( OR(AK="No",AK="Waiting on client"),AH<TODAY()-14)

so that will not work
you need the formula as i supplied, modified for No and B2 dates

=AND(OR(E2="no",E2="waiting for client"),B2<TODAY()-14)
and applied to cells E2 to E?? i have put E1000 rather than E:E

see attached

[newdoverman]

The attached workbook should give you what you describe.

[BeckyMaclean]

Woo hoo it works!

Thank you so much for your help etaf.

I think I figured out what I did wrong, I had only put 'AK' instead of 'AK4' and also hadn't specified where it applies to. I can't say I fully understand the $ in the 'applies to', but my formula works, so I'm happy. Thanks again!

[etaf]

the $ is absolute addressing so that does not change if you where to copy >paste special>formats

[newdoverman]

Here is another way of getting the conditional formatting:

[BeckyMaclean]

Thanks all for your help. I am struggling again.

I now want any cells that say 'yes' in the 'Venue Visit booked' column to go orange, if the 'venue visit complete' column says 'no' or is blank.

I've tried to do this so that it's just based on the 'yes' in 'venue visit booked' and the 'no' of venue visit complete, but it's not working. The formula I've used is:

=AND(AK6="YES", AO6="No")

Can someone help explain where I've gone wrong? Hopefully one day I will be able to work out my own formula rather than bugging everyone that way!

Also, will this new conditional formatting affect the other conditional formatting I have? If so, how do I go about resolving it?

[etaf]

whats not working exactly - looks correct

=AND(AK6="YES", AO6="No")
to include a blank

=AND(AK6="YES", OR(AO6="No", AO6=""))

put the formula into a cell in the spreadsheet and see if you get a true / false OK

[BB1972]

Becky,

The following code, in Cell F2 of the attached example sheet:

Please Login or Register  to view this content.

is what you require. The rule here is applied where either [D2 is blank AND F2 = "YES"], OR [D2 = "NO" AND F2 = "YES"].

I cannot troubleshoot the formula you say is not working, as you have not included the sheet you have it in.

Hope this helps.

[newdoverman]

Could you post what you are now working with as it seems there has been new cells entered into the mix and old parameters as originally stated no longer apply. This has become highly confusing to follow.

Eg. When did AK6 and AO6 get added to the mix and what is between the original data and these cells? It also appears that the original column D had dates in it but that isn't the case anymore..

[BeckyMaclean]

whats not working exactly - looks correct

=AND(AK6="YES", AO6="No")
to include a blank

=AND(AK6="YES", OR(AO6="No", AO6=""))

put the formula into a cell in the spreadsheet and see if you get a true / false OK

Thanks for your and BB1972's help. I think the problem was that my data started on row 4, rather than 6. When I changed it, it seems to have worked. That would explain it, right?

[BB1972]

I think the problem was that my data started on row 4, rather than 6. When I changed it, it seems to have worked. That would explain it, right?

Well, yes - you need to be referencing the correct cells in order to get the result you want.

Just to echo newdoverman's sentiments - if you're having difficulty with formulas etc, the best thing to do is to upload a representative sample of the workbook you're using. This will (should!) help avoid confusion when you start referring to ranges like AK etc that are not in the workbooks that have been uploaded so far.

Hope this helps

[newdoverman]

When you make changes to where data starts, there could be surprising consequences not foreseen by such a simple change.

[BeckyMaclean]

Thanks all for your help. There seems to be some sort of error on this thread as everytime I try and respond, it doesn't show up!