Hi,
First let me comment on the #number! error you recieve (sometimes) for distance from "home location" - it has been adressed recently in my other post:
http://www.excelforum.com/excel-prog...ml#post3622321
Going to main question:
I think it will be much easier to do this with macro than with formulas.
As you rate yourself as novice in Excel, I will show an easy way (probably quick enough but not quickest possible) to acomplish the task with just using a macro to "control" the process. While most of work will be done by the excel formulas and autofilter "engine".
Settinng the stage:
1) copy (manually or macro - pretty easy - registered one would do, we will include this in main macro) all location names to empty column - we have column E handy. We will remove names of used or rejected locations from this list.
2) keep control on number of available locations - in G4 formula (with some overhead):
3) H2 will act not only as "initial location", but also "current location", so in I2:J2 we will have its coordinates
and similar in J2
4) which will allow to change formula in column D2 to refer to current location, not "fixed" H2 (including opening comment):
and copy down
5) lets have a look on locations we shall remove from the list by setting autofilter to less or equal F2 (we will later on do this with macro, so registering this step)
something like
will be registered. In main macro we will improve this a bit and use to remove "too close" locations from column E
6) now let's look (may be again registering) on all data which have column E not empty (they are further away than 15 miles and moreover as we will make another steps - were not used and were not closer in previous steps too).
the first line was registered as afutofilter has been reset
7) in G6 we calculate minimum distance taking into account only filtered (visible) flocations:
8) and in H4 we will show the name of this location:
=INDEX(A:A,MATCH(G6,D:D,0))
9) some headers in some cells and textbox with comment (used also as button to call macro) see attachment.
So the stage setup is complete and we can start writing the macro.
Bookmarks