Expert: Bill Hermanson - 11/5/2008

Question
Hi Bill

I am trying to put together a spreadsheet that imports and updates data daily from a database.  I have no problems in writing the query to import the data and it is set to update automatically every hour and when the spreadsheet is opened.

However, the problem I have is that I only want to display data from the last 30 days. What I would like to be able to do as soon as a new point is entered in to the spreadsheet all the data associated with the oldest data point i.e. that from 30 days ago is automatically deleted as the relevant data is from the last month.
The format of the data is
column 1 Date, column 2 Data Value.
The date format is dd/mm/yyyy time
I have reasonable basic knowledge of Excel but limited experience with macros.  Any advice would be much appreciated.

Thanks

Phil

Answer
Phil,

  There is no excel function or equation that I can think of which will delete a row from a database, or which will erase data from a row. I can think of several ways to HIDE such data, but the only non-macro way to delete it is to manually, not automatically, do it.

  I don't do macros, as my profile  says, so if you are convinced you need a macro for this, then you'll need to go to another expert.

  I can think of several ways to get the data you want onto a sheet, though, but you specifically mentioned macros so maybe you don't want equations.  

  The way I have in mind, very briefly, would be to bring all your data into a big database.  In Excel 2003, you can allow this database to have 65,536 rows if needed!

  Then you can apply an ADVANCED FILTER to the database, with the criterion set to select only rows whose data is within 30 days of today's date.  Copy the selected records to another spot (instead of using 'filter in-place').  This isn't quite 'automatic', although if you are using a macro to import the data, you could simply add these advanced-filter commands to that macro.

  Instead of the ADVANCED FILER, which requires macro commands or manual menu-driven operations, you could (possibly) create a swath of DGET functions to extract the various records from the master database onto another sheet.  I'd have to give that a little more thought, and also would have to know what makes each record unique (other than the variable data within it; for example, does each row have a different, but predictable, date?)

  There's probably some other equation-type techniques which could be used also, but this is what comes to mind for now.

  Other than asking Tom, on this site, about macros, because that's his specialty, and not mine.

   >>> Bill