Help Excel, help with conditional formatting please

[Rubber Bullets]

Hi,

I'm creating an Excel document. It has four columns, the first is a date that a document was created, the second is a value X and the third a date X years later that the document needs to be reviewed. I have already got that third column to populate automatically using an EDATE formula and have set up conditional formatting so that the box is green when the document is in date, amber for the three months before it expires and red if it goes out of date.

So far so good.

Some of these documents refer to things that may also need auditing at the same time as review. I want to be able to put a simple Yes / No condition in the fourth column, and then I want each of those cells to first ignore it if it is No, but if it is a Yes to copy the red, amber green formatting from column 3. Ideally as there are a large number of rows in the document I would like a solution I can do once and apply to all rows.

Is this possible?

Many thanks in advance,

RB

 

[dysfunction]

If you attach a sample excel file I can add the extra bit in for you.

 

[Rubber Bullets]

Hi Dys,

That's really kind, sadly the document I'm working on is at work, and I'm now struggling to remember exactly how I did the conditional formatting

Is there any way that you could describe what I need to do?

Sorry

 

[dysfunction]

It's a little bit complex to describe how to get what you need done. As I would setup a Yes/No data validation and then do the similar conditional formatting you have already done but add in the Yes/No variable. It's a bit too complex to write it down step by step

I'm working at home tomorrow so if you could somehow attach a sample file here I could do it then

 

[Rubber Bullets]

Thank you, I'll get it off to you when I get in. Hopefully before 9. Thank you again.

 

[Scouse]

Thank you, I'll get it off to you when I get in. Hopefully before 9. Thank you again.

Just in case you get done for sending work information to someone else - why not send a cut-down version with dummy info eh?

 

[dysfunction]

How about this @Rubber Bullets

 

[dysfunction]

Actually this is a better version as it combines the "date" and "Yes/No" within the conditional format

 

[Rubber Bullets]

Just in case you get done for sending work information to someone else - why not send a cut-down version with dummy info eh?

Yes, that's what I was going to do. I tried to create one at home, but couldn't remember how I'd done the formatting, idiot that I am.

Anyway attached is the example.

I will now check out Dys's suggestions too, thank you very much for your time.

 

[dysfunction]

do you need more help or are you ok?

 

[dysfunction]

I added a bit more to your file. Hopefully gives you want you need.

 

[Rubber Bullets]

Well I am really dumb when it comes to Excel, I've used it on and off for ages, but only in the simplest ways possible. Your examples work, thank you, but I'm struggling to work out why they work, sorry

 

[dysfunction]

My conditional combines your "date + Yes" so it says for eg C1&C2 = "YesIn date" then format it a certain colour green, amber,red.
The other condition is that if it's a "no" just format it grey

I also added "Data Validation" in the Yes/No column so that it can't be anything else.

 

[Rubber Bullets]

Cross posted. Thank you, I think I can work that out.

I really do appreciate the time you've taken to help. Thanks