Help Excel, help with conditional formatting please

Rubber Bullets

FH is my second home
Joined
Dec 22, 2003
Messages
1,453
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
 

Rubber Bullets

FH is my second home
Joined
Dec 22, 2003
Messages
1,453
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 :oops:

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

Sorry :(
 

dysfunction

FH is my second home
Joined
Dec 22, 2003
Messages
9,709
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

FH is my second home
Joined
Dec 22, 2003
Messages
1,453
Thank you, I'll get it off to you when I get in. Hopefully before 9. Thank you again.
 

Scouse

Giant Thundercunt
FH Subscriber
Joined
Dec 22, 2003
Messages
35,979
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

FH is my second home
Joined
Dec 22, 2003
Messages
9,709
Actually this is a better version as it combines the "date" and "Yes/No" within the conditional format
 

Attachments

  • Example.zip
    7.9 KB · Views: 1

Rubber Bullets

FH is my second home
Joined
Dec 22, 2003
Messages
1,453
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.
 

Attachments

  • Example.zip
    9.7 KB · Views: 1

dysfunction

FH is my second home
Joined
Dec 22, 2003
Messages
9,709
I added a bit more to your file. Hopefully gives you want you need.
 

Attachments

  • RB_Example.zip
    9.3 KB · Views: 1

Rubber Bullets

FH is my second home
Joined
Dec 22, 2003
Messages
1,453
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

FH is my second home
Joined
Dec 22, 2003
Messages
9,709
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

FH is my second home
Joined
Dec 22, 2003
Messages
1,453
Cross posted. Thank you, I think I can work that out.

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

Users who are viewing this thread

Top Bottom