mileage expenses spreadsheet

[RBMS]

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]

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

spreadsheet:

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]

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]

wow, that's so much smarter than my solution

*bows*

[RBMS]

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.

[Bob Phillips]

i think sumifs only works in excel 2007 or better.

It does, it was introduced in Excel 2007. But, you can achieve the same results with an array formula or SUMPRODUCT, see http://www.xldynamic.com/source/xld.SUMPRODUCT.html

[RBMS]

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?

[Simon Lloyd]

Here's 3 starters for you:
http://office.microsoft.com/en-us/te...006088458.aspx
http://office.microsoft.com/en-us/te...006088462.aspx
http://office.microsoft.com/en-us/te...006088454.aspx

The last one is probably what you're looking for.

[RBMS]

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.
mileage lists.xlsx

any help/abuse more than welcome.

[Bob Phillips]

Post your workbook so we can see some actual data.