Need help with a speadsheet (Conditional formatting)

[DJMK4]

I need a spreadsheet which will need abit of conditional formatting.

What it will be is a list of clients which all have licences, when the clients licence is within 30 days of expiring I need excel to flag this up in a spreadsheet.

Download an example of something i quickly mocked up below:

www.dmoranda.co.uk/webupload/formatting.xls

as you can see I have something but it has some flaws, as you can see if there is no data in there, the status section will automatically mark it as "Product has expired"

Is there anyway I can get it to put nothing in there if there is no data?

Also is there a way for a physical pop-up in windows to pop up on the screen? if not I will have to just get it to format the cells a different color.

Also what I was looking at implementing was an output which shows in a cell how many days are left before the licence expires.

Thanks for your help.

 

[DJMK4]

i will give this another go tomorrow

 

[DJMK4]

Hmm it seems that the requirements have changed a little.

I have a section called "Expirary date" which lists a number of expirary dates which have been manually put in. I simple want all ones which are within 30 days from =now() to be shown in red, all else to be shown in green.

 

[DJMK4]

Well on those cells, make the conditional formatting where the cell value is 30 or less to make the cell background red.

But the cell is a date? not a single value? the only way i can get around doing it is by including another column called "Days left" then put in a formula to display the number of days left before it expires, then do conditional formatting on that column.

 

[DJMK4]

Ah. How about putting a formula in the conditional formatting then? Where date of expiry - Today's date = 30 days or less then red cell background.

Does that make sense?

Hmm sort of, have you got an example?