Create Dynamic Hyperlinks in Excel [Video]

Posted on July 14th, 2011 in Excel Howtos , Learn Excel - 40 comments

During one my recent training programs, a participant asked an interesting question.

I have a list of values. I would like to place a hyperlink in my workbook that would always take me to the last value in the list.

Something like this,

Dynamic Hyperlinks in Excel - Demo

Now, we all know that you can use HYPERLINK() function to create a hyperlink in Excel. Here is a detailed tutorial on hyperlinks in Excel.

But, how do we make the hyperlink dynamic?

Very simple, we just count how many values our list has and then link to the last cell’s address accordingly. See this video tutorial to understand how you can do it.

Dynamic Hyperlinks in Excel – Video Tutorial


Watch this video on Chandoo.org Youtube Channel

Download Example Workbook on Dynamic Hyperlinks

Click here to download this example workbook. Play with it to understand how this technique works.

Do you use Hyperlinks? Share your tips

I use hyperlinks in my Excel workbooks all the time. They are easy to create and they make my workbooks more accessible. Adding a hyperlink is the easiest way to impress your audience.

What about you? Do you use hyperlinks? What are your favorite uses / tips? Please share using comments.

More Tutorials on Hyperlinks in Excel

Check out these tutorials to learn more about Hyperlinks in Excel

PS: that is my hand-writing in the image above. I bought Genius Slim Tablet on my way back from Singapore. So I am playing with it.

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

40 Responses to “Create Dynamic Hyperlinks in Excel [Video]”

  1. Stephen says:

    I never thought of using dynamic links, it makes generating hyperlinks so much easier and I might be more inclinded to use them.

    I use hyperlinks for my own purposes of navigating a document, but find that other people just don't use them; they can use the internet, work complex machinery, but as soon as you put a number on a piece of paper, it's too much for them.....

  2. Kevin says:

    Problem with hyperlinks is that they only work within the context of MS Excel. If you save the file as a "pdf" or print to a pdf the links are gone.

  3. Khushnood Viccaji says:

    I use hyperlinks in workbooks containing data records, which are brought in from multiple source files. (To create such consolidated workbooks, I use Ron DeBruin's RDBMerge add-in).
    To setup the hyperlinks I created a few macros which I've saved as a .bas module which can be imported into any workbook. If anyone's interested, I can send them the .bas file as a text file.

    A couple of sample macros to create hyperlinks are given below.

    1. This code runs through the cells in the current column and sets up a hyperlink to the filename in the active cell, until it encounters a blank cell. The code assumes that the cells contain valid FULL FILENAMES.

    Sub CreateHyperlink_ActiveCellValue_LOOP()
    Do While Not IsEmpty(ActiveCell)
    ActiveSheet.Hyperlinks.Add _
    Anchor:=Selection, _
    Address:=ActiveCell.Value, SubAddress:="", _
    TextToDisplay:=ActiveCell.Value
    ActiveCell.Offset(1, 0).Select
    Loop
    End Sub

    2. This code runs through the cells in the current column and sets up a hyperlink at for the first record of each filename. This code assumes that the active cell column is sorted by the filename (otherwise, each time the code encounters a different value in the next cell, it will create a hyperlink)

    Sub CreateHyperlink_ActiveCellValue_FIRST_REC_LOOP()
    ' create a hyperlink to activecell value for
    ' each first record in the column

    Application.ScreenUpdating = False

    Do While Not IsEmpty(ActiveCell)
    If ActiveCell.Offset(-1, 0).Value = ActiveCell.Value Then
    ActiveCell.Offset(1, 0).Select
    Else
    ActiveSheet.Hyperlinks.Add _
    Anchor:=Selection, _
    Address:=ActiveCell.Value, SubAddress:="", _
    TextToDisplay:=ActiveCell.Value
    ActiveCell.Offset(1, 0).Select
    End If
    Loop
    MsgBox "DONE!", vbInformation
    End Sub

  4. Sébastien Labonne says:

    Interesting approach.

    I personnaly like to use traditionnal hyperlink (CTRL+k) to a named range or cell.

    That name could be dynamic or not.

    Sébastien

  5. Oh man, I know I have already plugged my rarely updated blog, but the latest article I wrote talks about using hyperlinks to create mouseover effects. That is by far my favorite use for hyperlinks.

  6. Fred says:

    Great! I have never use a dynamic hyperlink. Learn something new! 😉

  7. Jennifer says:

    That's a pretty nifty solution.

  8. Oleksiy says:

    Chandoo, it isn't necessary to include a file name in the formula if you are linking cell from the workbook: =HYPERLINK("#sheet1!"&last_row,"Add new")

    You can take a look at my workbook on the SkyDrive - it has more advanced hyperlink to jump to the top: https://skydrive.live.com/view.aspx?cid=D8C4BA5E02DC5FFB&resid=D8C4BA5E02DC5FFB%21255

  9. John says:

    Very cool and very practical tip Chandoo, thank you.

  10. John says:

    Viccaji thanks also for your comments. I would be interested to see your .bas file although I have to admit I don't what a .bas file is, is it like an add-in? I have used some of Ron's DeBruin’s macros to merge csv files and they have been fantastically useful.

    Send to otherjack@yahoo.com

    Cheers
    John

  11. tpmbrian says:

    In a previous position I used MS-Project for some major projects, but not everyone had a license, so I created a map of all the important data in MSP and hyperlinked to a shared excel file so everyone could see the latest project plans. I later linked sub projects to projects to project summaries, but beware, you have to open and close all the files from the bottom to ensure you get the updated value cascade to the top.

  12. Anshul says:

    Hi Chandoo

    I am yet to see the details, but if there is a gap in cells (if cell B13 & B14 are empty, and there is some detail in cell B17) then the hyperlink doesn't take to the last cell

    Anshul

  13. bill says:

    i tried to appply this "HYPERLINK" formula to labels on a chart. theorotically this seems like it should be possible since graph name labels will accept a reference formula. i have not been able to get it to work. any suggestions? getting any hyperlink formula to work within a graph would be nice, some VBA could set up the links to the series and then the graph could be dynamicly changed as a sort of drill down exercise.

  14. Hui... says:

    If there are any Gaps in the list this modification will still work:
    =HYPERLINK("[dynamic-hyperlink.xls]'dynamic hyperlink'!B"&MATCH("ZZZZZZZZZZ",B:B,1),"Link that always takes you to last value of the list")

  15. Matt says:

    Clever stuff! I can definitely see myself using this tip regularly. Thanks Chandoo!

  16. [...] week we learned how to create dynamic hyperlinks in Excel. Today, I want to show you something even cooler. An interactive dashboard based on hyperlinks, [...]

  17. Clarity says:

    I recently created a model for a client where I nested a VLOOKUP within HYPERLINK. This allows you to define the HYPERLINK path based on the data returned by the VLOOKUP.

  18. Chandoo says:

    @All.. thank you so much for the comments & love.

    @Sebastien: Yes, named range based approach would also work the same. Plus it looks elegant.

    @Oleksiy: Wow.. I didnot know that. Thank you so much for sharing it. Donut for you 🙂

    @Clarity: Interesting use of HYPERLINK with VLOOKUP...

  19. Joe Wazen says:

    Hello,

    if i have a column in the first excel sheet containing a list of table names and in the other sheets the fields and data of each of these table (1 table per sheet). the sheet names are the same as the table names in the first sheet.

    How can i create a hyperlink dynamically so that when i click on one of the table names, i open the related sheet??

    Regards,

    Joe

  20. Santosh Tiwari says:

    Hi Chandoo,

    I would like to know, how i can use hyperlink with my chart dynamically.Suppose i have bar chart and i want to hyperlink it with a picture so that whenever i click on any portion of my bar chart it shows me that picture.

    Santosh

  21. Santosh Tiwari says:

    Hi Chandoo

    I am trying to create a chart.I got inspiration for this chart from a link http://www.improving-visualisation.org/vis/id=137 . Please help in creating this kind of chart.

    Santosh Tiwari 

  22. Santosh Tiwari says:

    Hi Hui,

    Thanks for sending me link for hyperlink video.It was good one, but i am still wondering about how to create visualization which i had seen in   http://www.improving-visualisation.org/vis/id=137 . 

    Satosh Tiwari.

     

    • Hui... says:

      @Santosh
      Using the Coke Can example
      So you make a Stacked Column Chart with 1 Column and 5 Stacked Series
      Make a Coke Can outline with a white mask outside the Coke Can
      Make a Coke Can and set the Opacity to about 40% (I Use CorelPaint or Paint.Net (Free) )
      Place both pictures in front of the chart
      Add other text and numbers to suit

      Maybe pull this apart: https://www.dropbox.com/s/tffkx7g6uc9pfca/Coke.xlsx

  23. Santosh Tiwari says:

    Hi Hui,

    Thanks a lot for sending me coke file.....i wonder how i can add link to different color section of coke.Please help me in this regard.

    Santosh Tiwari 

    • Hui... says:

      @Santosh

       

      What do you mean by "Add link to different color sections"

      The color sections of the chart are different series in a chart which is linked to your data

      You can change the Label of the different series to link to something other than the Series value as is the case now

       

  24. Santosh Tiwari says:

    Hi Hui,

    If you go through the link which i had provided you, you will find that on placing cursor on different color section, it will show different data outside of chart.I am trying to create vba for mouse over function and by using link and photo shoot technique i am looking to create that kind of effect in my visualization.

    Santosh Tiwari
     

  25. safayet says:

    Hi... I have been trying to develop a hyperlink that should have the following features....

    the   hyper-linked  cell will show the name of workbook.
    As I change the worksheet name hyperlink will remain same but update according to worksheet name. 

    If you have any tips for this, please provide me that.... 
     

    • Hui... says:

      @Safayet

      When you say Change the Worksheet Name do you mean rename the current worksheet or change to a different worksheet?

      • safayet says:

        @ Hui

        Thank you for the concern... 🙂

        I mean renaming the current worksheet. I want a hyperlink that will work even if I rename the worksheet. 

        • Hui says:

          @Safayet
          This looks messy and it is but it works
          =HYPERLINK("[Book1.xlsx]" & RIGHT(CELL("filename",Sheet2!A1), LEN(CELL("filename", Sheet2!A1))-FIND("]", CELL("filename", Sheet2!A1))) & "!B4","CLICK HERE")
           
          This will link to Sheet2 cell B4 in Workbook Book1.xlsx

          Change the Workbook and Sheet name and Address name to suit

          If you have any problems with it first I suggest you retype all the " manually as they sometimes get screwed up

  26. Lco says:

    I've entered this, but it only brings me to the first cell of the sheet...
    =HYPERLINK("[nameoffile.xlsx]'sheename'!a"&(COUNTA(B:B)+1),"sheet note")
    I'm not sure what I'm doing wrong. I want it to go to the last empty cell of the sheet in row A, but I've tried several things I've seen on this page and none want to work. I'm using Office Professional 2007. Can someone help me?

    • Hui... says:

      @LCO

      The address needs to be as Text
      so: =HYPERLINK(ADDRESS(COUNTA(Sheet1!$B:$B)+1,1,,1,"[nameoffile.xlsx]Sheename"),"Sheet Note")
      Should do it.

      • Lco says:

        Thanks so much!!! - the only problem now is if the worksheet has a space in the name of it it stops working. I've been looking for a solution online but can't find one that works.

        • Michael (Micky) Avidan says:

          @Lco,

          Just in case you never found the answer to your question regarding a sheet with space(s) in it's name - here is the answer:

          Suppose the sheets name, you are refering to - in the same Workbook - is: Sheet ABC

          The following formula should work:

          =HYPERLINK("#'Sheet ABC'!"&ADDRESS(COUNTA('Sheet ABC'!B:B)+1,1),"Type friendly name here")

          Michael Avidan
          “Microsoft®” MVP – Excel
          ISRAEL

  27. russ says:

    Question. Assume one is working with two defined names that are being tracked through out the worksheet - Male and Female. The user will reach one spreed sheet (lets call it sheet 1) that will have a cell that states "Click here". How could one design a simple formula for that cell that if the user clicked on it and the user was male (which is already known), another spreadsheet (Spreadsheet Male) would open with charts for a male and if female another spreadsheet (Spreadsheet female) would open with charts for a female. Tried creating a process whereby on a calculation page there is hyperlink for male (a1) and female (a2) and an if formula =if(sec="Male",a1, a2). a1 would contain a hyperlink to  Spreadsheet Male and a2 would contain a hyperlink to Spreadsheet female. The only problem is that the formula will simply recognize the appropriate hyperlink but will not open the hyperlink. Is there any way to create an automatic opening of a hyperlink or a simpler way to do this. Thanks

  28. Michael (Micky) Avidan says:

    @Chandoo,
    Sorry for being so late 🙂
    You don't need the File Name nor the Sheets Name if you only need to "Jump" to the lat value in column "B" in the same Sheet.
    All you need is:
    =HYPERLINK("#"&ADDRESS(COUNTA(B:B),2),"Go to last value in column B")

    Michael Avidan
    “Microsoft®” MVP – Excel
    ISRAEL

  29. Michael (Micky) Avidan says:

    Sorry for the TIPO. The formula should read:

    =HYPERLINK("#"&ADDRESS(COUNTA(B:B)+1,2),"Go to last value in Column B")

    Michael Avidan
    “Microsoft®” MVP – Excel
    ISRAEL

Leave a Reply