Add rows on worksheet by date?

[Ed from AZ]

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

 

[Barb Reinhardt]

You could do it with a pivot table or SUMPRODUCT. I'll show the SUMPRODUCT
method

=SUMPRODUCT(--( A1:A100 = Date(2008,3,17) ),--( D1100 ))

Where column A is the date, column D is the calculated miles and this is
pulling data for 3/17/2008.

 

[Dave 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

 

[Dave Peterson]

In this case, you don't need to use the -- in front of the D1100.

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)),D1100)

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.

 

[Ed from AZ]

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

 

[Dave Peterson]

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/IntroductiontoUserFormsPartI.asp
Part II
http://msdn.microsoft.com/library/en-us/dnoffsol02/html/IntroductiontoUserFormsPartII.asp

 

[Ed from AZ]

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