Help with a milage log

Occasional Visitor

I am trying to create a sheet with drop downs for destination that would insert the correct mileage between the destinations. I have a cross reference mileage matrix on a sheet with the destinations down column 1 and matching destination across row 1 with the mileage between at the intersection points. I would like to create a separate sheet that has drop down boxes every other row cell for selecting the destinations in the matrix sheet and the mileage would auto populate from the matrix sheet in the cell between the destinations. The last cell in the row would total the miles. How do I insert the correct mileage in the cell between two destinations depending on the destinations selected? Can anyone point me to a tutorial or example that would teach me how to do this?

Matrix reference sheet looks like:

MileagesPoint 1Point 2Point 3

Point 1

02030
Point 220010
Point330100

I tried to drop my actual files in the post, but error states .xlsx file not supported.
Thanks for any help,

Charles

2 Replies

@charleswhite3 

You may apply data validation list as

image.png

and use

=INDEX( Mileages, MATCH(G4,Mileages[Mileages], 0), MATCH(H4, Mileages[#Headers], 0 ) )

to get mileage.

@charleswhite3 

 

This approach should work. See the attached file for the working example.

mathetes_0-1642794684007.png