Mileage report

[Jason S]

Im making a database to keep track of my miles. Id like
it to automatically insert the miles when I type in the To
and From information. I understand that I can list the
criteria of one place to next equals such and such, but
how would I code that in the main data window cells?
Jason

 

[Max]

Let's say you have assembled the sample data
below in Sheet1, in A1:C4
which shows the distances between any 2 points

From.....To.....Dist
A...........B.........5
A...........C.........8
A...........D........10

Put in D2: =TRIM(A2&B2)
Put in E2: =TRIM(B2&A2)

(Assumption: Distance A to B = Distance B to A)

Select D2:E2
Copy down to row4
---------------------------------
In another sheet, say Sheet2,
assume you have set-up
a table in A1:C3 (sample below)
with col C to return results
based on inputs made in cols A and B

From.....To.....Dist
B............A........5
A............C........8

Put in C2:

=IF(ISNA(OFFSET(Sheet1!$D$1,MATCH(TRIM(A3&B3),Sheet1!D,0)-1,-1,1,1)),IF(IS
NA(OFFSET(Sheet1!$E$1,MATCH(TRIM(A3&B3),Sheet1!E:E,0)-1,-2,1,1)),"-",OFFSET(
Sheet1!$E$1,MATCH(TRIM(A3&B3),Sheet1!E:E,0)-1,-2,1,1)),OFFSET(Sheet1!$D$1,MA
TCH(TRIM(A3&B3),Sheet1!D,0)-1,-1,1,1))

Copy down col C

Col C will return the distance/mileage figures from the sample data in
Sheet1
(if no match is found for the inputs in cols A and B, a "-" is returned)
(It's assumed that Distance A to B = Distance B to A)

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email