Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mileage Chart
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 a 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mileage Chart
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mileage Chart
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 "Tonyg" wrote in message oups.com... 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 a 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
chart to SUM mileage over months | Charts and Charting in Excel | |||
I'm using the mileage log., How do I total all the mileage? | New Users to Excel | |||
Gas Mileage | Excel Worksheet Functions | |||
chart: mileage MPG Fuel total paid | Charts and Charting in Excel | |||
Mileage Chart (I'm a beginner) | Excel Programming |