 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2008Add rows on worksheet by date?
I've been asked to create a spreadsheet that will track vehicle miles by route. The drivers will note the odometer when they start the route and the odometer when they return, so we can log that across a row: Vehicle - Route - Odo Start - Odo End - Calc Route Miles They also want miles by vehicel by day. Since a vehicle can travel more than one route in a day, is there an easy way to do this? My original thought was to have input through validation drop-downs to choose teh vehicle and the route - now I'm thinking of using a Form, and the Enter Data button will automatically sort by date and vehicle number to keep the same things together. But can I use a formula to calculate the miles by vehicle by date? Or would that have to be a macro code? Ed You could do it with a pivot table or SUMPRODUCT. I'll show the SUMPRODUCT method =SUMPRODUCT(--( A1:A100 = Date(2008,3,17) ),--( D1:D100 )) Where column A is the date, column D is the calculated miles and this is pulling data for 3/17/2008.  SignatureHTH, Barb Reinhardt > I've been asked to create a spreadsheet that will track vehicle miles > by route. The drivers will note the odometer when they start the [quoted text clipped - 12 lines] > > Ed In this case, you don't need to use the -- in front of the D1:D100. The -- is used to coerce things that look like numbers to numbers or even convert booleans (true/falses) to 1/0's. =SUMPRODUCT(--(A1:A100=Date(2008,3,17)),D1:D100) And in this case, since it's just dependent on one field (the date field), the OP could use: =sumif(a1:a100,date(2008,3,17),d1:d100) or =sumif(a:a,date(2008,3,17),d:d) =sumif() can use the entire column. =sumproduct() can only use the entire column in xl2007. > You could do it with a pivot table or SUMPRODUCT. I'll show the SUMPRODUCT > method [quoted text clipped - 24 lines] > > > > Ed SignatureDave Peterson This sounds like a nice project to learn about pivottables. You'll be able to create nice reports based on vehicle, route, or combinations. If you've never used pivottables, here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx > I've been asked to create a spreadsheet that will track vehicle miles > by route. The drivers will note the odometer when they start the [quoted text clipped - 12 lines] > > Ed SignatureDave Peterson Thanks, Dave. I've actually been wanting to get into pivt tables for a while - just never had a good excuse. Actually, the Boss says he can do pivots - but I kinda think he means for _me_ to do the number crunching, so I'll be ready! 8>) If I remember correctly, for a pivot table to work right, these should be sorted by: -- Vehicle -- Date -- Route yes?? (Or probably Date, then Vehicle, since more than one vhicle will run on one day. Would it make a difference?) I'm also thinking of going ahead with the validation drop-downs for data entry, but have a macro on workbook_close to do the sort. (I can force them to enable macros before they can open and use the book.) Does that sound like it'll work? Ed > This sounds like a nice project to learn about pivottables. > [quoted text clipped - 34 lines] > > - Show quoted text - Nope. The raw data that is used to create the pivottable doesn't have to be sorted. And you may want to look at Data|Form for data entry. If that's close to what you want, but not quite right, you could try one of these: John Walkenbach has an enhanced data form: http://j-walk.com/ss/dataform/index.htm And the source code is available for a small fee ($20 USA, IIRC). And if that's not sufficient, then maybe you could design your own input userform. Debra Dalgleish shares some tips: http://contextures.com/xlUserForm01.html Peter Aiken Articles: Part I http://msdn.microsoft.com/library/en-us/dnoffpro01/html/IntroductiontoUserFormsP artI.asp Part II http://msdn.microsoft.com/library/en-us/dnoffsol02/html/IntroductiontoUserFormsP artII.asp > Thanks, Dave. I've actually been wanting to get into pivt tables for > a while - just never had a good excuse. Actually, the Boss says he [quoted text clipped - 57 lines] > > > > - Show quoted text - SignatureDave Peterson Thanks so much, Dave!! I'll chew on these for a while and post back with more questions as I need to. I greatly appreciate the boost! Ed > Nope. The raw data that is used to create the pivottable doesn't have to be > sorted. [quoted text clipped - 84 lines] > > - Show quoted text -
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.