Results 1 to 2 of 2

Thread: Display history for spreadsheet data

  1. #1

    Display history for spreadsheet data

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    Excel Office 365 Insider
    It sounds like a good place to use a PivotTable to be honest.

    If you're able to use VBA to copy to prior history, could you copy all the data into another sheet in a PivotTable format, then update your column to pull from the PivotTable? You'll get a lot more functionality for slicing and dicing your data if you do...
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: -||- Blog: -||- Forums:
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts