Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-28-2012, 02:39 PM
cjaye cjaye is offline Display history for spreadsheet data Windows XP Display history for spreadsheet data Office 2007
Novice
Display history for spreadsheet data
 
Join Date: Mar 2012
Posts: 6
cjaye is on a distinguished road
Default Display history for spreadsheet data

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
Reply With Quote
  #2  
Old 04-09-2012, 04:03 PM
macropod's Avatar
macropod macropod is offline Display history for spreadsheet data Windows 7 64bit Display history for spreadsheet data Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,939
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Hi cjaye,

You should consider having all the data on a single sheet and, on another sheet, the formulae used for reporting. Coded correctly, the formulae can retrieve the data for whatever period(s) you're interested in. With over 1million rows to play with, it'll be a long time before the data sheet fills up - and you can always prune the data once they're too old to be of use.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 04-23-2012, 01:30 PM
cjaye cjaye is offline Display history for spreadsheet data Windows XP Display history for spreadsheet data Office 2007
Novice
Display history for spreadsheet data
 
Join Date: Mar 2012
Posts: 6
cjaye is on a distinguished road
Default

Thanks for the reply macropod. I'm currently working on (struggling) some vba code to copy previous mileage to a history sheet for each month, however, I think I may consider your suggestion of putting the formulas on another sheet... I might then be able to insert/hide the history columns on the data sheet (the user's preference)..since inserting columns which would push data & formulas over would not be an issue if the formulas were on a different sheet.

Here's my current vba code
Code:
  'Prior to the Previous Mileage column on the Main sheet being updated with the Current Mileage column (performed once at beginning of month),
   'this section copies the Previous Mileage to the Mileage History sheet to add to the mileage history
   'which helps Deanna better track/manage the mileages.
  
   'Holds last month/year (ex. December, 2011) for Mileage History sheet column heading
    LDate = (MonthName(DatePart("m", CurDate - 30))) & ", " & DatePart("yyyy", CurDate)
  
    Lc = Lastcol(Sheets("Mileage History")) + 1      'finds the first empty column on the Mileage History sheet
    Set sourceRange = Sheets("Main").Columns("C:C")  'sets Main.previous mileage column range
    Set destrange = Sheets("Mileage History").Columns(Lc) 'sets Mileage History column range to copy prev mileage to
    sourceRange.Copy destrange  'copy prev mileage
    
   'Adds a column heading of previous month, year, centers expands column width
    With destrange.Range("A1")
      .Value = LDate
      .HorizontalAlignment = xlCenter
      .ColumnWidth = 11
    End With
Reply With Quote
  #4  
Old 04-23-2012, 03:58 PM
macropod's Avatar
macropod macropod is offline Display history for spreadsheet data Windows 7 64bit Display history for spreadsheet data Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,939
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Hi cjaye,

What I said was:
Quote:
You should consider having all the data on a single sheet and, on another sheet, the formulae used for reporting.
With this approach, no macros are needed - it's simply a matter of re-writing the formulae so that they summarize the appropriate month's data from the data sheet. Plus, there's no need to hide anything and, in the longer term, you'll end up having more data for trend analysis, etc.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 05-01-2012, 12:34 PM
cjaye cjaye is offline Display history for spreadsheet data Windows XP Display history for spreadsheet data Office 2007
Novice
Display history for spreadsheet data
 
Join Date: Mar 2012
Posts: 6
cjaye is on a distinguished road
Default

I'll try your suggestion of putting formulas on a separate sheet but I'm not sure how I can get away with not using a macro. The data sheet has only a previous mileage column and a current mileage column, it doesn't have mileage for each month. And the end-user will not allow changes to the data sheet as far as what data goes in which columns. Maybe I'm not understanding your solution. Thank you anyway for your help. You've given me some things to think about.
Reply With Quote
  #6  
Old 05-01-2012, 03:34 PM
macropod's Avatar
macropod macropod is offline Display history for spreadsheet data Windows 7 64bit Display history for spreadsheet data Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,939
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
  #7  
Old 05-02-2012, 01:42 PM
cjaye cjaye is offline Display history for spreadsheet data Windows XP Display history for spreadsheet data Office 2007
Novice
Display history for spreadsheet data
 
Join Date: Mar 2012
Posts: 6
cjaye is on a distinguished road
Default Display history for spreadsheet data

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
File Type: xls Forum WB.xls (24.5 KB, 2 views)
Reply With Quote
  #8  
Old 05-03-2012, 02:26 AM
macropod's Avatar
macropod macropod is offline Display history for spreadsheet data Windows 7 64bit Display history for spreadsheet data Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,939
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Hi cjaye,

See attached. All you need to do is to insert each month's data on the 'Mileage History' sheet. Even the dates on that sheet are calculated automatically by copying the formula in column A down. Assuming you have more vehicles to monitor, simply add extra columns for them on the 'Mileage History' sheet and copy columns A:G on the 'Main' sheet.
Attached Files
File Type: xls cjaye.xls (33.0 KB, 3 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 05-03-2012, 06:27 AM
cjaye cjaye is offline Display history for spreadsheet data Windows XP Display history for spreadsheet data Office 2007
Novice
Display history for spreadsheet data
 
Join Date: Mar 2012
Posts: 6
cjaye is on a distinguished road
Default Display history for spreadsheet data

I was looking for a way to automate the whole process of capturing the history onto that Mileage History sheet. I don't want the administrator to have to manually copy data to the sheet (if I understood you correctly). My vb code above in the third post, copies the data to the history sheet and puts the current month heading in. This is done when she clicks on the macro button to update the Current Mileage column to the Previous Mileage column. Where it falls short is if a new vehicle is inserted or deleted. Not sure how to handle that...that's why was trying to try another method...like sycning the two sheets.

Thanks again for your help.
Reply With Quote
  #10  
Old 05-03-2012, 06:04 PM
macropod's Avatar
macropod macropod is offline Display history for spreadsheet data Windows 7 64bit Display history for spreadsheet data Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,939
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Hi cjaye,

Automating the whole process with vba is quite straightforward, even if new vehicles need to be catered for. Testing whether a given vehicle is listed on the 'Mileage History' sheet and adding a new column for that vehicle isn't at all difficult; neither is adding a new row for each month. Likewise, catering for a new vehicle on the 'Main' sheet appears to require nothing more complicated than copying columns A:G down one line - even if the addition of the new vehicle on the 'Mileage History' sheet entails inserting the new vehicle's column between a pair of the existing ones. That's a benefit of the formulae I've used on the 'Main' sheet.

Without knowing more about the format of the data you're processing, there's a limit to how much I can help you with the coding. As I said, though, it's all quite straightforward. See attached. The 'Demo' macro inserts some data for new & existing vehicles, then updates the main sheet's layout to reflect the new vehicle data.
Attached Files
File Type: xls cjaye.xls (68.5 KB, 6 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #11  
Old 05-08-2012, 08:03 AM
cjaye cjaye is offline Display history for spreadsheet data Windows XP Display history for spreadsheet data Office 2007
Novice
Display history for spreadsheet data
 
Join Date: Mar 2012
Posts: 6
cjaye is on a distinguished road
Default

Thank you very much Macropod for your time in helping me. You gave me some great ideas to consider. I knew there had to be a straight forward way to do this just didn't have the knowledge. My user wants the veh # along the left margin (there 500 or so), so I will try re-working your code to accomodate that. Thanks again.
Reply With Quote
  #12  
Old 05-08-2012, 04:22 PM
macropod's Avatar
macropod macropod is offline Display history for spreadsheet data Windows 7 64bit Display history for spreadsheet data Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,939
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Hi cjaye,

Here's the same thing with the alternate history orientation. Changes to code and formulae are required.
Attached Files
File Type: xls cjaye.xls (68.5 KB, 2 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Displaying cell data in another spreadsheet Colonel Biggs Excel 0 07-01-2010 07:37 PM
Client Payment History GMatos78 Office 0 04-22-2010 03:40 PM
Display history for spreadsheet data Data Set Display Size - PP 2007 TonyMan PowerPoint 1 04-14-2010 05:45 AM
Displaying data from spreadsheet in Userform darklide Excel 0 03-12-2010 05:42 AM
CAUTION!! Sorting a spreadsheet with hidden columns will trash your data. psmaster@earthlink.net Excel 0 11-24-2009 11:54 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 11:18 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft