Search code examples
excelexcel-formulaexcel-2013

Excel HYPERLINK function doesn't work

I've got a spreadsheet that includes Google Map links, but the links don't work. They're generated with this function:

=HYPERLINK("http://www.google.com/maps/place/" & F19 & "," & F20)

...where F19 and F20 contain lat and long coordinates in +/- format. It appears to work fine, produces a link and everything, but clicking on them just pops up a "An unexpected error has occurred" error.

I've googled the issue a bit, but all the solutions I've found seem to refer to earlier versions of Excel and revolve around registering old DLLs that can't be found on this system. Links automatically generated by just entering an URL in a cell also pop up the error, but simultaneously open the link properly.

Any advice?

Solution

  • I am following the following steps to Link Google Map URL's From the Excel Worksheet.

    A. Analysis of URL

    For example Google Map URL for India is : 

    https://www.google.co.in/maps/place/India/@20.1505368,64.4808042,4z/data=!3m1!4b1!4m5!3m4!1s0x30635ff06b92b791:0xd78c4fa1854213a6!8m2!3d20.593684!4d78.96288?hl=en

    It has following distinct parts.

    • a) https://www.google.co.in/maps/place/
    • b) Place whose map is required like "India"

    • c) Location Identifier after @ For India it is 

      20.1505368,64.4808042,4z

    • d) Location Data Identifier after "/data=" For India it is 

      !3m1!4b1!4m5!3m4!1s0x30635ff06b92b791:0xd78c4fa1854213a6!8m2!3d20.593684!4d78.96288?hl=en
      1. Now we put the respective parts in A4,B4,C4,D4 respectively.

      2. We use the following formula in E4 to concatenate the strings together. 

             =A4 & B4 & "/@" & C4 & "/data=" & D4

      3. Finally another cell for example in E8 we use the HYPERLINK Formula.

        =HYPERLINK(E4,"India ")

      4. Now it is a clickable link and we can open India's Map by clicking on the link.

    Finally snapshot pictorially depicts it.

    Snapshot showing Google Maps Hyperlink process

    EDIT 28-06-2016 Regarding problems raised in the question my viewpoint is mentioned against each point.

    Links automatically generated by just entering an URL in a cell also pop up the error, but simultaneously open the link properly.

    Regarding problem occurring when full hyperlink is entered. Other checks are to made. Is it a casual phenomenon even after clearing the cache and restarting the system or a regular one. If it persists on a new instance of excel after clearing cache( I use CCleaner free version) then problem may please be taken up with Microsoft Community

    ...where F19 and F20 contain lat and long coordinates in +/- format. It appears to work fine, produces a link and everything, but clicking on them just pops up a "An unexpected error has occurred" error.

    This problem could have linkage with the problem mentioned above. It can also be caused by a minor syntax problem relating to concatenation of strings. I faced similar problems relating to proper concatenation of strings of the URL and could solve it after a number of attempts in syntax correcting. Finally I found that constructing URL is easier and without much of syntax problems, If I put various elements of the URL in the cells of the worksheet and from their values construct the final URL.