+ Reply to Thread
Results 1 to 12 of 12

Help with Mileage Log to record specific distances

  1. #1
    Registered User
    Join Date
    11-02-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Angry Help with Mileage Log to record specific distances

    I'm creating a mileage log for my work as a traveling art teacher. Most templates have you just input the number on the odometer when you started and when you finished. Since I can never remember to check the odometer when I am getting in and out of the car I thought it might be easier to just record the distance from each location once and then record that number of miles each trip. I usually travel the same route so the mileage is always the same.

    I have an array of numbers right now, with 3 columns listing my 3 most common start locations. Then I have 5 rows listing the 5 schools I travel to for work, and the distances from each start to each end in the array. On a separate sheet I have my log.

    What I would like to be able to do is select a start location and an end location from a drop down list on the log and have the number of miles automatically appear in the miles column. Then I can double this for the return trip and total it at the bottom and calculate my expense.

    I am having trouble writing a formula that works for all 15 possible distances, and I don't quite know how to write it so that it references my array and finds the correct distance for those two specific choices. So far I have the drop down menu's pulling from the array but I'm stuck at how to index the array using the information that is coming from the drop down list.

    Since I travel to these locations multiple times I think it would be a lot faster than looking up the distance every time and typing it up manually.

    This seemed simple to me to figure out at first but I've been wracking my brain over it for a day now and the solution is still not coming to me. Any help would be appreciated. Thanks! I have attached my file if it makes it easier to look at it in excel.
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Help with Mileage Log to record specific distances

    Try this
    =INDEX(Sheet1!$B$9:$F$12,MATCH(B9,Sheet1!$A$9:$A$12,0),MATCH(C9,Sheet1!$B$8:$F$8,0))

    to take into account no entries
    =IFERROR(INDEX(Sheet1!$B$9:$F$12,MATCH(B9,Sheet1!$A$9:$A$12,0),MATCH(C9,Sheet1!$B$8:$F$8,0)),0)

    see attached
    Attached Files Attached Files
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    11-02-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Help with Mileage Log to record specific distances

    Thanks so much this works perfectly!! I knew I was getting close but I just wasn't typing it in quite right. Thank you this problem was really bugging me!! Thank you! Thank you! Thank you!
    Tricia M.

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Help with Mileage Log to record specific distances

    Excellent ,your welcome

  5. #5
    Registered User
    Join Date
    01-22-2014
    Location
    Allen, Texas
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Help with Mileage Log to record specific distances

    Hi Tricia and etaf,
    Thank you so much for sharing this file. I am a traveling speech-language pathologist and I've been wanting to create the same type of log! I travel to up to 6 patients' homes per day, sometimes stopping at my own home (office). I'm hoping to find a way to log more than one stop for each row. So from Home to Patient A to Patient B to Patient C and back to Home. If Patient B cancels, then I would need to go from Home to A to C to Home. It seems that the table will be much more complex....


    Any suggestions??

    Thanks so much!
    - Kate

  6. #6
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Help with Mileage Log to record specific distances

    K8bobate

    Welcome to the forum.

    Pls start your own thread as per forum rules.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  7. #7
    Registered User
    Join Date
    01-22-2014
    Location
    Allen, Texas
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Help with Mileage Log to record specific distances

    Oops, sorry, Fotis. I will do that now.

    BTW, my family is from Greece We visit Crete often, not so much the mainland.

    Kate

  8. #8
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Help with Mileage Log to record specific distances

    You have to visit some other islands too. Of course mainland has great places to visit. Take a look to the links in my Signature!

  9. #9
    Registered User
    Join Date
    01-25-2015
    Location
    Near Milwaukee, WI USA Earth Milky Way
    MS-Off Ver
    2010
    Posts
    10

    Re: Help with Mileage Log: NEED ANOTHER FEATURE PLEASE!

    Please see following message. Not seeing how to delete....

    ADMIN PLEASE DELETE THIS ONE!

    Thanks / Sorry...
    Last edited by mlwinnig; 01-25-2015 at 08:05 PM.

  10. #10
    Registered User
    Join Date
    01-25-2015
    Location
    Near Milwaukee, WI USA Earth Milky Way
    MS-Off Ver
    2010
    Posts
    10

    Question Re: Help with Mileage Log: NEED ANOTHER FEATURE PLEASE!

    Hi all. I too have been looking for something very much like this, but my needs are a bit different. I don't need to deal with reimbursement (per se), just calculate miles drive for IRS. I have many locations and will need to create new ones (or just enter the miles driven) on the fly.

    WHAT I AM LOOKING FOR is for the spreadsheet to take yesterdays ending mileage, use it to populate todays starting mileage (I will override for personal use miles if necessary) and calculate new ending mileage based upon miles driven.

    If you can help with this, I would be willing to make a small donation to you or your favorite cause.

    If not clear on the need, please ask.

    Thanks in advance!
    Last edited by mlwinnig; 01-25-2015 at 08:02 PM.

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help with Mileage Log to record specific distances

    mlwinning, welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  12. #12
    Registered User
    Join Date
    01-25-2015
    Location
    Near Milwaukee, WI USA Earth Milky Way
    MS-Off Ver
    2010
    Posts
    10

    Re: Help with Mileage Log to record specific distances

    Got it, thanks. Sorry...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 11-16-2012, 09:25 AM
  2. Replies: 5
    Last Post: 10-01-2012, 12:11 PM
  3. Replies: 3
    Last Post: 07-06-2011, 01:42 PM
  4. Link a txtbox value to a specific record?
    By SAsplin in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 08-28-2009, 11:29 AM
  5. Locate specific record
    By burl_rfc in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-23-2006, 05:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1