[Az-Geocaching] Generating Hyperlinks from Waypoints in Excel
Team Tierra Buena
az-geocaching@listserv.snaptek.com
Wed, 27 Feb 2002 20:58:22 -0700
This is a multi-part message in MIME format.
------=_NextPart_000_001D_01C1BFD1.7ED93A00
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
If you download the cache data files from the Snaptek site (as always,
thanks Brian and Jason!) and import them into Excel 2000 or Excel 2002
(XP), here's a formula that will generate a hyperlink to the web page
for any cache in the listing.
In the first blank column (Column "I" in the "az_caches_team" file,
Column "G" in the "az_caches" file) of Row 2, enter the following
formula:
=Hyperlink("http://www.geocaching.com/seek/cache_details.asp?ID="&Hex2De
c(Right(C2,4)))
It will evaluate to the Web link of the cache that has its waypoint
listed in cell C2. You should then be able to click on it and, assuming
you are connected to the Internet, automatically open a browser window
containing that cache's web page. Once you've checked that the formula
is working, you can drag that formula down all the rows of the list, and
generate a hyperlink for each line.
If you enter this formula and get an Excel #NAME? error, you probably
need to install and/or enable the "Analysis ToolPak" Excel add-in. Go to
"Tools, Add-ins" on the Excel menu bar.
You can actually use this formula to convert a waypoint in any
spreadsheet into a link. Just replace "C2" in the above formula with the
cell address that contains the waypoint. As the formula is written, the
waypoint must be six characters in the format GCxxxx. To illustrate, the
waypoint must be in the cell as GC0057 not GC57.
Hope you find this useful.
Steve
Team Tierra Buena
------=_NextPart_000_001D_01C1BFD1.7ED93A00
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<TITLE>Message</TITLE>
<META content=3D"MSHTML 6.00.2713.1100" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT face=3D"Book Antiqua">If you download the cache data files =
from the=20
Snaptek site (as always, thanks Brian and Jason!) and import them into =
Excel=20
2000 or Excel 2002 (XP), here's a formula that will generate a hyperlink =
to the=20
web page for any cache in the listing.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Book Antiqua">In the first blank column (Column =
"I" <SPAN=20
class=3D744125303-28022002>in </SPAN>the "az_caches_team" file, Column=20
"G" <SPAN class=3D744125303-28022002>in</SPAN> the "az_caches" =
file) of Row=20
2, enter the following formula: </FONT></DIV>
<DIV> </DIV>
<DIV><FONT=20
face=3D"Book =
Antiqua">=3DHyperlink("http://www.geocaching.com/seek/cache_details.asp?I=
D=3D"&Hex2Dec(Right(C2,4)))</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Book Antiqua">It will evaluate to the Web link of the =
cache=20
that has its waypoint listed in cell C2. You should then be able to =
click on it=20
and, assuming you are connected to the Internet, <SPAN=20
class=3D744125303-28022002>automatically </SPAN>open a browser window =
containing=20
that cache's web page. Once you've checked <SPAN=20
class=3D744125303-28022002>that the formula is working</SPAN>, =
you <SPAN=20
class=3D744125303-28022002>can</SPAN> drag that formula down all the =
rows of the=20
list, and generate a hyperlink for each line.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Book Antiqua">If you enter this formula and get an =
Excel #NAME?=20
error, you probably need to install and/or enable the "Analysis ToolPak" =
Excel=20
add-in. Go to "Tools, Add-ins" on the Excel menu bar.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Book Antiqua">You can actually use this formula to =
convert a=20
waypoint in any spreadsheet into a link. Just replace "C2" in the above =
formula=20
with the cell address that contains the waypoint. As the formula is =
written, the=20
waypoint must be six characters in the format GCxxxx. To illustrate, the =
waypoint must be in the cell as GC0057 not GC57.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Book Antiqua">Hope you find this useful.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Book Antiqua">Steve<BR>Team Tierra=20
Buena</FONT></DIV></BODY></HTML>
------=_NextPart_000_001D_01C1BFD1.7ED93A00--