Hi cjaye,



Even without data for the dates, you can probably use formulae alone to retrieve the data for the latest month. If you can post a sample workbook with a couple of months' data, I'll take a look an see what might be possible. That will also be helpful if a macro-based solution is required.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Attached is a very basic sample workbook with two sheets. The Mileage History sheet is a sample of what I'm hoping to automate. There is a lot of vb code I did not include that manages emailing various mileage missing/incorrect notifications out to users, I figured you didn't need to see that. The spreadsheet administrator does not want the spreadsheet altered as far as column placement, etc. I am sure there is a more efficent way to manage this whole process but at this point... at the beginning of each month the administrator updates the Previous Mileage column by clicking on a macro/button that simply copies the Current Mileage column to the Previous Mileage column and overriding it. The administrator receives mileages throughout the month which she posts to the Current Mileage column....at the end of the month, if vehicle owners have not turned in a current mileage, their gas keys are turned off... and the process starts all over again the next month. There is no history kept. The process/spreadsheet is setup to just receive mileages, notifications are emailed out throughout the month for those vehicles where she hasn't rec'd a current mileage yet. After a third notification at the end of the month, their gas key is turned off. The user would like to see some kind of quick reference history...mainly to make it easier for her to monitor mileages... for example, if she receives a mileage that doesn't makes sense...lower than previous mileage or something...she can refer to the history to get an idea what might be going on with that vehicle. I'm not a strong Excel user (vb, pivots, formulas, lookup/filters), but I'm looking at either syncing the Main sheet with a Mileage History sheet, Vlookup methods, vb, or formulas. I have to also take into consideration...whatever solution I use, I will need to allow for insert and deleting rows. If the user deletes or inserts a row on the Main sheet, it should be deleted or inserted from the history probably.
Using Excel 2007 to develop this
Spreadsheet administrator uses Excel 2003
Approx 500 rows are in the Main sheet of the workbook
Lotus 8.5.2
XP
Thanks very much for any help and/or direction.

Attached Files

Forum WB.xls (24.5 KB, 10 views)