# mileage expenses spreadsheet

#### RBMS

##### New member
Hi

Not sure if this is the right area to post this in (mod's feel free to move it) but here goes.

I am trying to create a spreadsheet for the directors and management staff to claim their mileage expenses. I have created tables in excel with total miles between each possible starting point and destination. I want to create the spreadsheet so that we can just select the various destinations travelled to (for instance all journeys begin at livingston and most will end here too) in order and the total mileage travelled is totalled at the end of the row. there will be a row per day for each (up to 31) day of the month. My question is, what would be the best formaula to use to extract the correct data from the correct table depending on the previous location (either start point or most previous destination). The fact that there are 35 seperate tables as we have a possible 35 sites that we visit at various times, is causing me a little bit of a headache. I am by no means even remotely confident in my excel abilities but am keen to learn, so any help, pointers, abuse would be most appreciated.

Regards

H

#### JYool

##### New member
I would have my data in one big table, then you can use sumifs to match the mileage amount to the start point column, and the end point column

data:
 start finish mileage point A point B 100 point A Point C 150 point A Point D 200 point A Point E 250 point A Point F 300 Point B Point A 101 Point B Point C 151 Point B Point D 201 Point B Point E 251 Point B Point F 301

 Date start point end point mileage 8/1/2011 point a point b 100

formula for mileage is =SUMIFS(C:C,A:A, f2,B:B, G2)

where c:C is the column containing the mileage numbers, a:a is the start points, B;B is the finish points in the data, and f2 is the user entered start point, and g2 is the user entered end point (has to match perfectly to work)

i would use data validation on a named range of all the location gchoices to create a pull down list that way you know that it`s actually going to match the data.

you can use the indirect formula to make it look in seperate tables if you don`t want to merge all the data into one place.

i think sumifs only works in excel 2007 or better.

#### Bob Phillips

##### Super Moderator
Staff member
Have a matrix like so

............. Livingston....Stranraer....Arbroath....etc
Livingston......-...............40............50..............
Stranraer.......40.............-..............25..............
Arbroath........50............25.............-................

and use MATCH to match column and row and INDEX to get the mileage.

#### JYool

##### New member
wow, that's so much smarter than my solution

*bows*

#### RBMS

##### New member
Have a matrix like so

............. Livingston....Stranraer....Arbroath....etc
Livingston......-...............40............50..............
Stranraer.......40.............-..............25..............
Arbroath........50............25.............-................

and use MATCH to match column and row and INDEX to get the mileage.

dude thats so simple yet so clever. will give that a try. might need to pop back and ask for hints on how to use MATCH and INDEX commands.

Staff member

#### RBMS

##### New member
ok so i have my massive table of data (mileages) but I am totally stumped as to what to do next. my initial thoughts were to have my data hidden, but have the spreadsheet on the front with the following columns:

day|start|dest1|mileage|dest2|mileage|dest3|mileage|dest4|mileage|dest5|mileage| total mileage

where mileage is the miles travelled between destinations. and the total mileage is obviously the total of all individual mileages. should be simple yet my brain cannot figure it out.

MATCH and INDEX?

#### RBMS

##### New member
I have already downloaded these templates, but they still require an awful lot of filling in manual and looking up tables for mileages etc. The second one is kind of what i would like to create, but given we have 35 different sites (36 including HO) the lookups get a little complicated for a novice like me.

i've attached a copy of the mileages tables, to give you an idea of how much data there is to select from.
Also on the next page of the attached spreadsheet is roughly how i thought it would look if i could get the formulae correct.
View attachment mileage lists.xlsx

any help/abuse more than welcome.

#### Bob Phillips

##### Super Moderator
Staff member
Post your workbook so we can see some actual data.

#### RBMS

##### New member
hi bob,

i think i just did. see that attachment above.

Al

#### Bob Phillips

##### Super Moderator
Staff member
Ah yes, as it was a reply to Simon and didn't read on.

Try this.

BTW, I notice some errors. In your tables, Bathgate to Falkirk is is 11 miles, Falkirk to Bathgate is 21 miles. Some one-way system. I have highlighted the discrepancies.

#### Attachments

• XLGuru - 421 - mileage lists.xlsx
44.5 KB · Views: 17

#### RBMS

##### New member
Hi Bob

Thanks for that man. I thought there might be a few discrepancies with distances, but that was a minor issue that can be sorted at the later stages.
What I am wanting to have on the claim sheet is the drop down menus in the destination columns (that I can do), and then the mileage between locations auto appearing in the relevant cells, and totalled up at the end of each row. It must be possible?

Al

#### Bob Phillips

##### Super Moderator
Staff member
Doesn't my worksheet do just that?

#### RBMS

##### New member
It does, just without the drop down menus. It works perfectly as is though.

#### RBMS

##### New member
Also, I take it I just need to add the functions to each mileage cell but change the relevant info in each cell?

#### Bob Phillips

##### Super Moderator
Staff member
Well, that is simple enough

#### Attachments

• XLGuru - 421 - mileage lists.xlsx
47.4 KB · Views: 32
Last edited:

#### RBMS

##### New member
Dude, thanks a bunch. That's save me a good few hours of head scratching.

#### Simon Lloyd

If you want the starting point to also be dropdwon selection you can simply drag the first cell of destinations (D7) to C7 then copy down.

#### snelmica

##### New member
WOW, Match and Index are SWEET! Never seen that and went overboard completing this thing to try it out.

Hey dude, i did this for you using the uber-cool Match & Index / Matrix idea -- and just for comparison used a VLOOKUP solution (which required me to rearrange your named-ranges on the mileage tab and add a combined names column to base the search on).

Check it out (i took liberty interpreting what you were doing with the multiple destination columns...but you will get the idea).

The red row contain the VLOOKUP solution based on the really long list (view the attachement) and the blue ones the much cooler match/index method. Enjoy.

#### Attachments

• mileage_snelmica.xlsm
67.7 KB · Views: 22