I am trying to create a workbook for use by 5 different employees that will
track the amount of miles each employee drives during work for purposes of
reimbursement. Each worksheet will be for a separate employee. On the
worksheet I need to have the following columns: Date, Description of travel,
Start, End, Mileage (I will also have a column for the running total mileage,
but that is separate from this question). To make it easier for them to
input this information, i would like the worksheet to automatically calculate
the mileage to and from common locations. For example i would like the user
to be able to select in the start column either work or home and in the end
column a range of 10-15 locations (these location in the End column will be
the same for each user). I don't know how to set this up. How can I have
excel calculate mileage based on the selection of home or office and the end
location, and even more complicated...how can i have this setup since the
distance for home to any location will be different for each employee. Is
there a simple way to do this or would it be easier to create separate
workbooks for each user. The benefit of having all users work in one
workbook is that I will be able to have access it all at once and create
charts/other reports without having to switch between workbooks. I know this
is complicated and don't know if there is even a solution...but thanks in
advance for any and all advice.

Hi,

I would create a table like the following on a seperate sheet

------- Mike-Home Mike-Work Bob-Home Bob-Work
Home 0 25 0 15
Work 25 0 15 0
Office 20 5 10 5
Deopt 18 7 12 13

and on another sheet have a cell with a drop down menu with (Home,Work) and
another with (Home,Work,Office,Depot), on the mileage cell I would use
something like:

=INDEX(Sheet2!B3:E6,MATCH(C2,Sheet2!A3:A5),MATCH(A 1&"-"&B2,Sheet2!
B2:E2,0))

where A1 is the name of an employee, B2 is a start location (Home,Work) and
C2 is destination (Home,Work,Office,Depot)

Hope you can use this to get you started.

Regards
Jean-Guy

"Ruben Torrez" wrote:

I am trying to create a workbook for use by 5 different employees that will
track the amount of miles each employee drives during work for purposes of
reimbursement. Each worksheet will be for a separate employee. On the
worksheet I need to have the following columns: Date, Description of travel,
Start, End, Mileage (I will also have a column for the running total mileage,
but that is separate from this question). To make it easier for them to
input this information, i would like the worksheet to automatically calculate
the mileage to and from common locations. For example i would like the user
to be able to select in the start column either work or home and in the end
column a range of 10-15 locations (these location in the End column will be
the same for each user). I don't know how to set this up. How can I have
excel calculate mileage based on the selection of home or office and the end
location, and even more complicated...how can i have this setup since the
distance for home to any location will be different for each employee. Is
there a simple way to do this or would it be easier to create separate
workbooks for each user. The benefit of having all users work in one
workbook is that I will be able to have access it all at once and create
charts/other reports without having to switch between workbooks. I know this
is complicated and don't know if there is even a solution...but thanks in
advance for any and all advice.

Thanks, This ended up being the code i used
=INDEX(Sheet2!$H$7:$L$11,(MATCH(D4,Sheet2!$H$7:$H$ 11,0)),(MATCH(B1&C4,Sheet2!$H$7:$L$7,0)))

for an index on sheet2 that looked like this:
MikeHome MikeWork BobHome BobWork
Home 1 2 3 4
Work 5 6 7 8
Office 9 10 11 12
Depot 13 14 15 16