# Distance rate calculator

1. ## Distance rate calculator

Hello I was wondering if someone could help me to create a distance calculator. At present I have a spreadsheet using vlookup formulas so where a person can choose a town froma drop down list and the distance to that town from a particular suburb will automatically be displayed with the vlookup formula.

However I want to add to this so the person can first choose a different from suburb then choose the TO suburb. For example at present my spreadhseet will only show the distance from Melbourne to the suburb chosen from the drop down list.
I want to be able to first choose the departure town so it might be Melbourne or Sydney or Perth, then the destination town and depending on this the distance between the two is shown in the cell.
I have a file containing my departure towns and the distances to each suburb from them.
Im just not sure how to do the first part so my vlookup formula for the destination will use a different range depending on what departure town was selected.
Any help would be appreciated.

Thanks heaps

2. ## Re: help creating a distance rate calculator?

You'll need dependent validation list.

Look here:

http://www.contextures.com/xlDataVal02.html

3. ## Re: help creating a distance rate calculator?

Actually, I think this is a simple two dimensional INDEX/MATCH problem.

Take a look at this sample sheet and particularly the two-dimensional table. If you put your distance table into a standard 2D table like that (I've seen many distance maps laid out this way over the years), then this should work nicely for you.

INDEX-MATCH-Simple.xls

NOTE: the "table" is on the second sheet. Instead of Colors and Animals, you would just have two sets of city names.

4. ## Re: help creating a distance rate calculator?

Another example:

5. ## Re: help creating a distance rate calculator?

Thanks WHER, that's a great standard example of a distance matrix. Do you mind if I incorporate that into my sample sheet permanently?

=======
Truman,

If these suggestions takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

6. ## Re: Distance rate calculator

It would be an honour, sir.
Seriously, this was a task/assignment during an Excel course i took last year.

7. ## Re: Distance rate calculator

Thanks for the help from everyone. I can use the dependant data validation to select a suburb destination depending on what departure point I select however Im not sure what formulas to use to show my distances. My suburb lists are very long with hundreds of suburbs so I cant set it up similar to the distances 2d table example shown my Jbeaucaire.
And as we have a limit of 200kms some towns are more than 200kms from the departure point so are not in the list so each departure point has a different destination list. Ive included an example here but its just a short selection of my suburb list.
If I had the same suburbs for each destination I could have it setup as per distance2.xls and then could use the INDEX/INDEX/MATCH with named ranges as per Jbeaucaires example.
Thanks again for any help.

Just to explain my example file distance.xls as Adams estate is 165 kms from CAM its in the distance list, but as its more than 200kms from CLAY and HAL its not in their lists........So increase your distances I hear someone say....problem is no matter what distance we use there is always going to be suburbs that are out of range from one or more of our departure towns....Hope Im making sense...LOL

8. ## Re: Distance rate calculator

Bump....Someone???? Anyone??????

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