Mileage Chart

[Tonyg]

I am creating a chart by zip codes that will give the mileage between
numerous zip codes. Once the data is entered I would like to use one
cell to enter the start zip code and one for the finish zip code and
have the data in the appropriiate cell the result. I have tried array,
logic, and matrix functions but have not been able to come up with the
correct formula.

23320 23321 23322 23323 23324

23320 1 12 14 16 5

23321 12 1 13 15 17

23322 14 13 1 10 12

23323 16 15 10 1 11

23324 5 17 12 11 1

The formulas that I need help with are:

Start Zip Code 23321
End Zip Code 23324

Mileage 17

There are 64 zip codes to work with and the only way I know how is to
use logic functions that are limited as to the number of functions
within a single cell or formula or are just too labor intensive adding
all of the functions. I know that I usually end up taking the long way
around but in this case there are too many zones to work with.

Thanks

 

[stevebriz]

Tony,
something along the lines of this should work for you

Create a userfrom with 2 text boxes, 1 commandbutton and 1 label and
also a close buttom
Add command button to you spreadsheet to open the user form

On the spreadsheet
Enter all the zip codes in row starting a column B
Enter all the zip codes in column 1 starting a row
Fill in all the appropriate mileages


Then in the commandbutton1_click ( your enter or get mileage button)
Code the following

Dim I as integer
Dim J as integer
i = val(textbox1.value) - 23318

J = val(textbox2.value) - 23318
Label1.caption = cells(I,J).value

You might want to put some error checking to ensure the textbox entry
is numeric and also in the range of the Zip codes. ( this will prevent
errors coming up)

Any questions?..let me know.

 

[Peter T]

One way, amongst many -

sample data in A1:F6
pair of zip codes in B10:B11

=OFFSET(A1,MATCH(B10,B1:F1,0),MATCH(B11,A2:A6,0))

Regards,
Peter T