Help from advanced Excel users

Slayerx

I will try explain the issue best I can.

I have to log certificates on a spreadsheet.

The expiry date is the date we issue against a certificate, once it gets to 6 weeks before it runs out we have to write to request the company apply to be re-accredited.

We use filters to have the companies in expiry date order and we manually work out 6 weeks in the future.

I was wondering it there was an easier way to do this ?

Can excel and other office products talk to each other so it would automatically flag a reminder in outlook for example ?

Wombat

Yes, they can.

You can probably adapt this code for your needs:

http://www.jpsoftwaretech.com/using-excel-vba-to-set-up-task-reminders-in-outlook/

Ainsley1

[written before Wombat's good example above! grrrrrrrr, well done Wombat]
Answer to questions:
Yes,
yes and
yes

firstly you could add a function to each certificate entry in a blank cell to check if the expiry date is equal to (or less than) today plus (six weeks times seven days). Say the expiry date was in cell B3 and B4 was to be used to flag up the prospective expiry then function =if(or(B3<(Today() +6*7),B3=(Today() +6*7)),"Soon", "Plenty time")

You can then include this soon or plenty result in your filter cutting out some of the manual operation.

Excel can communicate with outlook so that, for example, you could automatically trigger an email for those soon to expire. It's a bit beyond me tonight but I think it would mean writing code (Visual basic) in Excel or maybe better Outlook. It used to be called Office Automation in older versions of Office -simply put using the facilities of one program from another- but may well be called something different in the up to date versions. It did used to need a fair bit of code writing (rather than just a few Excel functions in cells) by a programmer.

A compromise might be possible by having hyperlinks for each company's email address alongside each certificate? i.e. having the hyperlink as an email address so that you can, with one click open Outlook with the preset email address to send a message. (lookup hyperlink in help for info or just select insert hyperlink and select the suitable bits!)

Slayerx

Thanks I'll have a look at your replies and see which is most effective

EchoLocation

Have a look at this thread discussing counting down days to a deadline / expiry etc. see if you can take anything from it.