Delete work sheet / book on a nominated date Excel 2007

[Banner]

Hi, I currently send out work sheets to various departments on a monthly basis and consistently get out dated / previous versions returned to me.
Is there any way I can automatically delete information on a given date rendering the sheet useless?

[dominicb]

Good afternoon Banner

Is there any way I can automatically delete information on a given date rendering the sheet useless?

You can, but Excel isn't entirely suited to this kind of thing because it depends on users OK'ing the use of macros, and you can't force them to.

This article by Chip Pearson might be a good place to start :
http://www.cpearson.com/excel/workbooktimebomb.aspx

HTH

DominicB

[Banner]

Thanks - will take a look straight away!

[brokenbiscuits]

For a macro-free solution, you could maybe create 'copies' of each sheet with a formula based on the expiry date which you could hold in a separate sheet (perhaps a cover sheet if you already have one).

For instance, if you had a sheet called Sheet1, you could hide this sheet then create a new sheet Sheet2 with the following formula in each cell:

A1:
=if(today()>'Cover Sheet'!$A$1,"",Sheet1!A1)

Drag this along and down to cover as many cells as are held in Sheet1, and copy over the formatting of Sheet1. This will create a sheet which visually matches Sheet1, but if today's date is greater than the expiry date held in Cover Sheet cell A1, then all the information would be blanked out.

It's not an ideal solution, especially if people need to see the formulas, but it may work for your requirements and negates the need for users to click 'enable macros'.

[Banner]

Thanks once again.