Use a VLOOKUP formula:

Either list your destinations in a spare column with the distances in the
next column to the right - say G1:H3 or make a named list: Insert Name
Define
Give it a name - say "To" (without the quotes) and then in the "Refers to"
box enter:

={"FHS",3.5;"CMS",9;"FMS",0.2}

(filled out to the full list)

Note that there is a comma between the Destination and the milage and a semi
colon between each set of Destination/milage data.

then use:

=IF(B1="Admin",VLOOKUP(A1,G1:H3,2),"") for the worksheet list or

=IF(B1="Admin",VLOOKUP(A1,To,2),"") for the defined name list
--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"Tech_in_the_woods" wrote in
message ...
I am trying to create a mileage tracker sheet based upon a pre-set table of
distances. I would like to be able to choose 2 different locations from
drop
down lists in 2 different columns, then have Excel input the mileage
between
them in a new column and eventually autosum the column. I tried this using
a
series of nested IFs then realized I would run into the 7 IF limit. My
problem is that I am not a developer and don't work in VB.
Here are the nested IF statements I was using: rather bulky but they
worked:

=IF(AND(B3="ADMIN",C3="FHS"),3.5,IF(AND(B3="ADMIN" ,C3="CMS"),9,IF(AND(B3="ADMIN",C3="FMS"),0.2,IF(AN D(B3="ADMIN",C3="LOR"),0.8,IF(AND(B3="ADMIN",C3="A BR"),10,IF(AND(B3="ADMIN",C3="ARA"),1,IF(AND(B3="A DMIN",C3="PAT"),9.2)))))))

My problem is that I have to add 3 more ifs in this section alone, then
have
the same thing for the 10 other locations. any suggestions would be
appreciated.
--
You want me to do what?!?!