Need help with a speadsheet (Conditional formatting)

DJMK4 · 21 Aug 2007 at 16:14

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.

ic1male · 21 Aug 2007 at 18:23

I've had a quick look - if there is nothing there then you can just add an extra IF statement to your formula along the lines of

=IF(C6="","", then continue with your original string of IF statements...)

MarcLister · 21 Aug 2007 at 18:32

For the empty spaces, how about =IF(C6="","No product data,"")

That should make Excel check for C6 having no text in the cell. If there isn't, it'll say "No product data". If there is, it shouldn't add anything.

As for the date, you'll need to compare the expiry date with today's date. =DAYS360(21/08/2007,20/10/2007,TRUE) returns 59 days between today and the 20th of October 2007.

You could then do some conditional formatting on the number of days left. Say where days left is less than or equal to 30, then make the cell background red. Where days left is greater than 30 but less than 61 make it green.

DJMK4 · 21 Aug 2007 at 21:12

i will give this another go tomorrow

MarcLister · 22 Aug 2007 at 14:27

Any luck on this?

Tomsk · 22 Aug 2007 at 14:37

=IF(C3="","",IF(C3-$A$3<=0,"Product has expired",IF(C3-$A$3<=7, "Insurance Expired",INT(C3-$A$3)&" days Insurance Valid")))

MarcLister · 22 Aug 2007 at 14:49

That looks good Tomsk.

DJMK4 · 22 Aug 2007 at 15:11

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.

MarcLister · 22 Aug 2007 at 15:14

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

DJMK4 · 22 Aug 2007 at 16:23

MarcLister said:
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.

MarcLister · 22 Aug 2007 at 16:25

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?

DJMK4 · 22 Aug 2007 at 17:44

MarcLister said:
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?

MarcLister · 22 Aug 2007 at 17:52

I've done something for you in Excel. Its the same spreadsheet as the one you put up for download last night.

Shall I email it to the address in your trust?