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.
Bookmarks