Dynamic pick lists (Basic Travelling Salesman code)

[dleizer]

Folks

I am relatively new to Excel (used it for years as a basic user) and this ismy first posting to any site (sorry if I break some rules)

I am trying to come up with appropriate code to do the following

I have a table which lists towns and routes between them

I want to come up with appropriate code to allow the user to select manually a given route between up to 10 towns

The way I see it working is as follows:

The sheet will have a table with headings Starting Town, Route Number, End town

There will be option to select up to 10 start and end points

• The user will select the first town (from a pick list)
• The user will then use a second pick list to select a given route out of that town. i.e i will need to create dynamically a list of all the routes leaving that town

• Once the user select the route the name of the related End Town will be automatically populated to the End Town cell and to the Start Town cell on the next line and the all process starts again until the last route is selected and the final end town is reached

Clear as mud?

E.g Assuming I have this town/route combination in a separate sheet

From Town To Town Route #

A B 1A
A B 1B
A C 2
A D 3
B E 4
B D 5
B F 6
C D 7
C G 8

If the user type Town A as the start town and move to the route field. the field validation at this instance will limit the options to routes 1A,1B, 2 & 3

If he then select route 2 the End Town will be set to C and at the same time the Start Town on the next line will also be set to C

Once he go to select the next route he will be given the option to pick route 7 & 8 and so it goes till he gets to last town

Can anybody tell me how to do that?

Thanks for your help

Don

 

[Taul]

It sounds like you are describing data validation (drop down lists) that are dependent on the previous selection. If so, take a look here

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

 

[dleizer]

Paul H <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Thanks for your quick response but before I try and work through the suggestion you send me (I am relatively new to the game so it will take some effort but that is the fun part) I would like to double check with you to make sure that we are on the same page.<o></o>
Does this example work by pre creating individual name ranges for each combination i.e one for fruit one for vegetables etc ? <o></o>
If that is the case I suspect that I can’t use that for I can’t predefine all the various town to route combinations (there may be 100s of towns in the list even though I am limiting the scope of selecting a maximum of 10 towns one can travel through in a given trip (i.e. start at A and end in X via 8 other towns. And not allowed to go through a given route/town more than once)<o></o>
<o> </o>
Just to recap my data set currently contains a listing with 3 columns (Starting Town, Route No, Ending Town) <o></o>
<o> </o>
Do you still believe that the suggested method should work or can you offer alternative solutions (with or without VBA)?<o></o>

 

[Taul]

If the user type Town A as the start town and move to the route field. the field validation at this instance will limit the options to routes 1A,1B, 2 & 3

If he then select route 2 the End Town will be set to C and at the same time the Start Town on the next line will also be set to C

Once he go to select the next route he will be given the option to pick route 7 & 8 and so it goes till he gets to last town

Selecting the routes as indicated above could be achieved with the dependant validation method.
However, I have a feeling this is a smaller part of a larger project. Travalling Salesmen tend to go off piste from time to time, therefore if you are trying to define routes between known customers, there may be a better approach.

Either buy the specialist software that would rationalise the route for either the shortest or quickest. Or you could build your own and link it with google maps.

Basically, if you go into Google maps and use post codes and type in a start point, you can also list the post codes of the "Via" locations, leading up to the end point. GM will then provide you with a route and distance. It is possible to link this with Excel, but is this what you were ultimately trying to achieve?

 

[dleizer]

Paul,

I appreciate your persistence

Maybe I should not have make reference to the Travelling Salesman example for I know that the algorithm behind that beast is scary (I am sure many a PHDs have done their thesis on that topic)

My problem is somewhat simpler (or so I thought) in as much that all I am trying to do is establishing the distances travelled between point a and point b if I know the route a person pick

As I said in my initial comment I expect the person to pick the route taken from town to town manually and once he get to the last town (point b) excel will calculate the total distance

What I managed to do already is to create a data entry grid with 10 fields (e.g Cells A2 to A11 with the validation of all of them set to point to a range name called routes which listed all of the routes (maybe I should call them roads from now on) which the person had to travel.

That solution works except that each time you select a road you had to pick it from the total list (no allowance made to the fact that roadAA is not connected to roadZZ)

What I am now trying to do now is to make the program easier to use by introducing some filtering on the field validation so that once one type the name of a town and then went to select the road leading out of that town one would only have the option of selecting the 3 or 4 roads which pass through that town and not the 100s of roads in my database.

I am not a SQL expert but I suspect that if I had the option of constructing the validation on the Road selection field in SQL I would have come up with something like “Select RoadID from TOWN_ROADS_TBL where StartPoint = TownName “
(startpoint holds the name of the town I am starting at nd endpoint is the twon I am going to

If I had an option of making it even smarter I would have change that statement to read “Select RoadID, EndPoint from TOWN_ROADS_TBL where StartPoint = TownName “ so as to show the user the name of the road and the name of the town it leads to
In the above examples StartPoint holds the name of the town I am starting at and EndPoint is the town I am going to

Am i am making things clearer? Probably not!!

Can one do something like this and if so how?

 

[Taul]

I think that takes you back to the dependant validation approach, which can be a bit laborious to set-up.
Hopefully someone else will post with an alternative approach to this problem.

 

[dleizer]

Paul

I managed to solve my problem (i took the liberty of attending a day course and in so doing managed to get the instructor to come up with an acceptable solution

The way it was done was using the match and offset commands
To be honest at that stage i've yet to figure out how it works but i was able to apply it to my application

the logic behind it is as follows:

The cell with the picklist has the following formula on the validation source


=OFFSET(Sheet1!$A$9,Sheet1!$D$25,2,Sheet1!$D$26-Sheet1!$D$25+1,1)

The list of towns start at a10 (not sure why offset is pointing to $a$9)
B25 contains the name of the town i am starting with
D25 contains =MATCH(B25,A10:A19,0)
D26 contains =MATCH(B25,A10:A19)

The list of roads start at c10 eg if town A has 3 roads leaving it there would be 3 entries for town A in A10,A11,A12 and the three roads would be listed at C10,C11& C12

As i said above i did not had a chance to work out the logic but it looks like the two match statements provides the starting and end location of the selected town in the town listing and i assume that the offset function dynamically build the road listing for a given town . Nifty

Once again i would like thank you for your interest and perseverance (was your solution similar to this?)

Cheers

 

[Taul]

Hey, I’m just pleased you have a solution, looks like a neat one too.

cheers
Paul.