# Need a formula that will calculate miles between multiple destinations

1. ## Need a formula that will calculate miles between multiple destinations

Hello everyone,
I am in the military currently in Afghanistan trying to create a excel spreadsheet that will calculate miles. I would like for it to simultaneously create a visual chart showing the work from each location to each location. I have included an example spreadsheet with make believe numbers and letters. I just need the formula so that I could apply it to my chart but if you can use the example chart well than a picture is worth a thousand words.
Example of what I would like for it to do:
1. Click on beginning location in this case it will be any place between A2 thru A20. (Automatically creates a Start Point SP) in A22
2. Click on any destination between B1 thru T1 (Automatically populates A23 with (name) and B23 the miles it takes to get to said location).
3. Click on follow on destination also between B1 thru T1 (Automatically populates A24 with (name) and B24 the miles it takes to get to said location) and so on and so on.
4. Finally the total will be displayed always in B41 the number changing as you click from destination to destination to destination.
I would very much greatly appreciate any assistance on this matter.

2. ## Re: Need a formula that will calculate miles between multiple destinations

paste this formula into B23 then copy down

=VLOOKUP(A22,\$A\$2:\$T\$20,(MATCH(A23,\$A\$1:\$T\$1,0)),0)

i also would make cells A22 to A40 select from a drop down list becuase if you dont type the exact names from above the formula doesnt work. to do this...

select cell A23, click on data ribbon, data validation, in the "allow" drop down box click "list".
"source" now opens up below choose =\$A\$2:\$A\$20
"ok"

then drag this down to A40

3. ## Re: Need a formula that will calculate miles between multiple destinations

Thank you for responding i will give it a try and see what happens if anything i will probably learn something new which is always a good thing thanks. I will let you know if it works

4. ## Re: Need a formula that will calculate miles between multiple destinations

You can try this. I basically did what the guy said to do just incase you didn't know how.

What it is is a list of drop downs so you can choose the starting point and destinations and then it calculates the distances and still totals them at the bottom.

For Forum Distances.xlsx

Let me know if that's what you want.

Jeremy

5. ## Re: Need a formula that will calculate miles between multiple destinations

Yes! both of you helped me very much thank you i just looked over the example and there is no way i would of figured out that formula in the time I was given to work on this thank you once again you made a difference with my unit and the unit that will be replacing us in a year's time. I will surely pass this on to them and I am sure they will appreciate as well.

6. ## Re: Need a formula that will calculate miles between multiple destinations

Hi! Hoping to get some help. I have a mileage form I'm currently working on. I didn't build it but I'm trying to help someone update it.
They've created a drop down list too on the first workbook page. When two sites are selected the total miles auto populates and so does a total.
We recently added a few new sites. I was able to figure out the data validation part of it to add the new sites to the drop down.
However, their mileage is not calculating. Any suggestions would be welcomed and greatly appreciated. Thank you!!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1