mileage expenses spreadsheet

RBMS

New member
Joined
Jun 9, 2011
Messages
10
Reaction score
0
Points
0
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
Joined
Aug 24, 2011
Messages
17
Reaction score
0
Points
0
Location
Courtenay, BC, Canada
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:
startfinishmileage
point Apoint B100
point APoint C150
point APoint D200
point APoint E250
point APoint F300
Point BPoint A101
Point BPoint C151
Point BPoint D201
Point BPoint E251
Point BPoint F301


spreadsheet:
Datestart point end pointmileage
8/1/2011point apoint b100


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
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
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.
 

RBMS

New member
Joined
Jun 9, 2011
Messages
10
Reaction score
0
Points
0
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.
 

RBMS

New member
Joined
Jun 9, 2011
Messages
10
Reaction score
0
Points
0
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
Joined
Jun 9, 2011
Messages
10
Reaction score
0
Points
0
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
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Post your workbook so we can see some actual data.
 

RBMS

New member
Joined
Jun 9, 2011
Messages
10
Reaction score
0
Points
0
hi bob,

i think i just did. see that attachment above.

Al
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
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
Joined
Jun 9, 2011
Messages
10
Reaction score
0
Points
0
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
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Doesn't my worksheet do just that?
 

RBMS

New member
Joined
Jun 9, 2011
Messages
10
Reaction score
0
Points
0
It does, just without the drop down menus. It works perfectly as is though.
 

RBMS

New member
Joined
Jun 9, 2011
Messages
10
Reaction score
0
Points
0
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
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Well, that is simple enough
 

Attachments

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

RBMS

New member
Joined
Jun 9, 2011
Messages
10
Reaction score
0
Points
0
Dude, thanks a bunch. That's save me a good few hours of head scratching.
 

Simon Lloyd

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
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
Joined
Sep 9, 2011
Messages
5
Reaction score
0
Points
0
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
Top