I'm looking for a way for my customer to have easy access to a year's worth of monthly mileage history for each vehicle on a mileage tracking spreadsheet.

I have created a spreadsheet for a customer that has vehicle and vehicle owner information in it with the main purpose of tracking vehicle mileages (current and previous) for each month. At the beginning of a month, my customer clicks on a macro button that copies the mileages from the current mileage column to the previous mileage column and then clears the current milege column in preparation for the new month's mileages. There is also vba code that accepts my customer's selection of 1st, 2nd, 3rd, and gas cutoff notifications throughout the month and automatically emails (thank you Ron De Bruin), to the POCs (point of contacts) on the spreadsheet, a list of their vehicles they have either, failed to submit mileages for, or have previously submitted monthly mileages that didn't meet a certain standard. Formulas in the spreadsheet and vba code are dependent on specific conditions which need to be in certain columns of the spreadsheet. For one example, in order for the vba code to work correctly, the POC information has to be in columns J,K, & L.

At first I was picturing saving the mileage history to hidden columns that could be unhidden when needed but I don't think I could do that because it would push data into the wrong columns causing my vba not to work properly. And then I was envisioning the mouse hovering over a field in the spreadsheet, like the vehicle number, and the mileage history would pop up in a 'comment looking bubble' or something to that effect. Or maybe keep the history on another worksheet and sync the two sheets.

Hoping someone out there has a cool solution and maybe a little detail that might work in my situation or at least point me in a good direction.

Thank you for your time.

Lotus 8.5.2 email


Excel 2007 saved as 97-2003 workbook
Excel/vba skill level - toddler