List Favourites like a POI - Method

glynny

Hello All,

Finally got a working Version.....

This little technique will enable you to create a Favourites File ready to load into Destinator that will behave like a POI :

1. Will identify the nearest location and tell you mileage.
2. Be sorted so that nearest locations are listed first NOT Alphabetically.

Unfortunately it can not do this "On the move" - you have to set up the spreadsheet with the location that you want all other locations sorted against and pre-load thru the Destinator Console.

Start a new Spreadsheet.

1. The top row is for the CURRENT LOCATION - this is what we will sort against.

Cell A1 - =TEXT(E1,"000.0")&" Miles "&B1
Cell B1 -"CURRENT LOCATION"
Cell C1 - Current Decimal Longitude (e.g -1.25639 typical for UK)
Cell D1 - Current Decimal Latitude (e.g. 52.21256 typical for UK)
Cell A5 - =3956*ACOS(COS(RADIANS(90-$C$1))*COS(RADIANS(90-C1))+
SIN(RADIANS(90-$C$1))*SIN(RADIANS(90-C1))*COS(RADIANS($D$1-D1)))

I'll just explain these quickly :
A1
Formula to join the contents of Cell E1 (Distance Calc) & Cell B1(Address) merging Text & Numbers and limiting Excels 15 digit calcs to 1 decimal place. You must have the 3 zeros as Destinator will resort Alphanumerically. This is what will be displayed in a Destinator Favourite.

B1
Address Text - as much or as little detail as you like. Remember Destinator is limited in the amount it can display so key descriptions early in the cell is best.

C1 Decimal Longitude (Destinator can display or take a look at streetmap)

D1 Decimal Latitude (Destinator can display or take a look at streetmap)

E1 Distance Calculation - this is the "As the Crow Flies" Calc NOT the shortest road trip - so be aware with Estuaries etc . (Cornwall to South Wales is not all that far "As the Crow Flies" but still a massive drive.)

2. Copy the remaining of your favourites into cells B2 B3 B4 etc etc. with associated Lats / Longs in C2 and D2 positions respectively.

3. Copy Cell A1 - Highlight Cells A2 to A?? as far as all records and paste in the Cell A1 formula. This will paste the formula to all rows but maintain the Formula with respect to other columns.

4. Do the same with Cell E1.

5. Go to TOOLS;OPTIONS;VIEW and make sure Show Zero Values is Ticked.

6. Go to TOOLS ;OPTIONS;CALCULATION and make sure Precision As Displayed is Ticked.

7. Highlight Column A then select "Sort Increasing" Icon on the toolbar.(A-Z with a down arrow.)

8. A dialogue will pop up asking what do you want to do. Expand Selection is default. Select SORT.

Your Spreadheet should now have 5 completed colums sorted into closest by distance to the CURRENT LOCATION. (If you select Sort Ascending - it will list the other way around - Furthest away first - A POI can't do that !)

9. Save your file twice
Once as an Excel File, FAVOURITE.XLS and secondly as a csv file FAVOURITE.csv

10. Start up Destinator Console, Tick your country map, and select Favourites Import, browse to your FAVOURITE.csv file and select "Convert" then "Download"

You should now have a Favourite in Destinator sorted by either "closest to "or "furthest away from" a particular position.

To recalc the list - go back to the original excel Sheet, FAVOURITES.xls and change the Lat & Long in Row 1 to new values.
As you change the values in a cell and move to the next cell the calcs will be updated. When the second figure is added the distances will be calculated relative to that point. If you now view Column 1 you will find that the sorting is probably wrong.

Highlight Column1, Select Expand Selection, Sort. You are now resorted in "closest to" fashion to the new location just entered.

At this point you do not have to Save the Excel file - you already have one, but YOU MUST SAVE AS... a .csv file keeping the same name, if you want to, or like I do as LONDON_SORTED.csv , GLASGOW_Sortedcsv etc

If you do keep the same name & upload to your PDa it will overwrite your previous version by a new sorted list relative to your new location.

I hope this is easy to follow and that if you use it that you find it useful.

If you have any difficulty please drop me an EMail and I can send you a Blank Excel file ready for entering Favourites and current location.

If anyone has any suggestions or comments or something they would like to see included then please let me know.

Glynny
_________________
Acer N35
Destinator 3

I can never think of anything clever to put here.....

	PocketGPS
	Web