Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



How Do I Automatically Hyperlink To The Reference In A Cell?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hello,

I have searched the board and can't find the answer to this particular hyperlink question.

I want to be able to create a hyperlink in a cell that is the result of a simple reference formula. i.e. if cell A1 on worksheet A is a formula "='SheetB"!A1", can I create a hyperlink automatically from SheetA Cell A1 to 'SheetB'!A1?

I would like for the hyperlink to be is cell A1, so I beleive that would be a macro / VBA solution.

Also, is there a formula solution that could go in B1 "=hyperlink(A1)", where the formula will then recognize the referenced cell (rather that the formula result) and hyperlink to SheetB:A1?

Hope all of that is clear.

Thanks...Dean

View Answers     

Similar Excel Tutorials

Linking Cells in Excel
In this tutorial I am going to cover how to link cells together. This is a useful feature of Excel as you can link ...
Dynamic Named Range in Excel
How to create a named range that expands automatically when a new value is added to the range. This is a great lit ...
Automatically Shade Every Other Row When You Add Data in Excel
How to make Excel automatically add row shading to every other row when you add data to the spreadsheet. This does ...
How to Find and Understand Excel Functions
In this tutorial I am going to show you how to find and understand new Excel Functions. If you go to the Formulas t ...

Helpful Excel Macros

Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Highlight Cells which Contain Formulas
- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight

Similar Topics







Hello everyone,

I would appreciate help with the following:

I want to have a macro follow the hyperlink in cell E5. I've tried several thinks from previous posts but the macro would select the cell rather than follow the hyperlink in it.

Details: I have the following hyperlink formula in cell E5 [=HYPERLINK($D$4)]
where $D$4 = CONCATENATE("[PM T&AS.xlsm]",C4) [PM T&AS.xlsm] is the file name C4 gives the cell name of the desired destination (through a formula), i.e. B7 Please note that the hyperlink is to go to a cell in the same worksheet and that the hyperlink varies according to the data given by cell C4.

Thank you in advance.

Best.


I am trying to link a hyperlink in a formula using VLOOKUP. I can get my new cell to reference the cell I want it to reference, but it will not carry the hyperlink with it. Any suggestions?

Example:
cell A2=VLOOKUP(C2,Profiles,5,FALSE)
The reference cell contains a hyperlink, but A2 will not send me to the hyperlink.

Thanks,
Chris


Hi there,
I have a column of cells with a formula in each to return a result . (about 40 rows)

I want to add a hyperlink to each one referencing an individual htm page.
The htm pages increments by one, example, page1.htm, page2.htm etc

At the minute Im right clicking each cell individually and selecting Hyperlink... and adding the name to the Address section.

I cant use =HYPERLINK because there is a formula in each one.

Is there a way to copy/paste the hyperlink to each cell and increment by 1 in the same way as adding text to the cell and drag/drop


Hello Everyone I have a hyperlink question and cannot find a solution anywhere.


Basic problem: i cannot figure out how to use HYPERLINK to Go to a cell in the same workbook.

Sub Problem: the cell that will contain the hyperlink will be dynamic based on a lookup.

=HYPERLINK(VLOOKUP(B1,DataArray,1,FALSE), "Link" )

Is this possible?
Could it be possible to write a GOTO function to do this if it cannot be done with formulas?


Thanks!


This HAS to be a simple fix.... Could not find a string with this solution in it yet.

Trying to set up a hyperlink to reference another location in the same sheet.
Used the example in Excel as a model to set this up so placed this formula in cell B1:
=hyperlink([Book1]Sheet1!a1,"LINK")
this defaults to =hyperlink(Sheet1!a1,"LINK") since Book1 is the current wb.
clicking on "link" in cell B1 produces "Cannot open specified file" error.
I need the hyperlink to be dynamic, so I can't use the CTRL+K hyperlink function.

Thanks in advance for any help




I have the following formula....

=IF(Paste_Sheet!H4="Error",HYPERLINK("#Paste_Sheet!"&CELL("address",P4),H4),Paste_Sheet!H4)

I have a sheet called lets say Sheet1..
On that sheet it it has a cells that reference PASTE_SHEET
If Sheet H4 has the result of "Error" (text) then I want a hyperlink applied...
The above formula works but I am not seeing the "Error" word on Sheet 1 but the hyperlink goes to the correct reference on the PASTE_SHEET.

Also the ones that don't have error as a result also have a hyper link on them.
But it say reference cannot open specified file.

What the result I am trying to get is the following:

If the Cell on sheet 1 = "error" then put a hyperlink to the paste_sheet cell reference and leave the wording of error on the sheet1 cell
If it does not do not put a hyperlink in the cell and just leave the cell reference...
I have to missing something..

Can someone help?

Appreciate the help...

kevin



First post, thanks for this great tool, finally have come up with a question I can't find the answer by searching for on the site.

I'm trying to create a hyperlink from a cell in a workbook to another sheet in the same book that will display the contents of the linked cell. I've been able to get that far, but trying to autofill or copy the formula means I have to manually edit every single formula to move to the next cell down. Example of my goal/setup:

Sheet 1: Hyperlink that displays an individual's name (which is listed on Sheet 2)
=HYPERLINK("#'Sheet 2'!B4",'Sheet 2'!B4)
=HYPERLINK("#'Sheet 2'!B5",'Sheet 2'!B5)
=HYPERLINK("#'Sheet 2'!B6",'Sheet 2'!B6)

Sheet 2: List of names
Bob
Jim
Tom

The link location is what does not autofill correctly, the friendly name does automatically move down the column when dragged. Thanks in advance!

Shribbles

I found this solution for "drop down list with hyperlink" but it did not work.

Perhaps a better solution is to use a workaround that relies on the HYPERLINK function to refer to whatever is selected in the drop-down list. For instance, if you have your data validation drop-down list in cell A1, then you might put the following formula in cell B1:

=HYPERLINK(A1, "Goto Link")

The solution directly above provides exactly what I am looking for
in the field where I write the formula, but it fails to hyperlink.
I have created a drop down list and linked each one of them to a
specific worksheet. When I select them individually they link to
appropriate worksheet. But when I select them in the drop down
list I receive the following error when I select the Hyperlink in
cell B1 as directed above.

"Cannot open the specified file"

Any thoughts?

Bob


ok i was wondering if this is even possible.
i want a hyperlink to reference to the contents of another cell example

REFERENCE TABLE

| A | B |
1 |Alexander | Sheet1 |
2 | Alona | Sheet5 |
3 | Alfie | Sheet3 |


Column A is the hyperlinks. column B is the exact name of another worksheet in this workbook. i want the hyperlink to reference to the cells in column B so when the hyperlinks are clicked the active (or visible) sheet is the sheet being referenced. example, when cell A1 (Alexander) is click it will take you to (reference B1) Sheet1. is this possible? i know i can make a simple hyperlink by right clicking and going to hyperlink and then just choose the "place in this document" but i have many many many many hyperlinks to make, i was hoping i can do it formula wise.




Column A lists how many links should be created... a number from 1 through 4.
Columns B, C, D, and E correspond to those 4 photo links.

How do I set up a formula such that, if...

A2 = 1, then B2 will create a hyperlink
A2 = 2, then B2, and C2 will create a hyperlink
A2 = 3, then B2, C2, and D2 will create a hyperlink
A2 = 4, then B2, C2, D2, and E2 will create a hyperlink

I do know how to create a hyperlink in a cell, so I don't need help with that.
In which cell would I put in the formula? F2?
I was thinking that a vlookup would help, but it would populate B2, C2, D2, and E2 when populating those cells is conditional based on what A2 will show (either a 1, 2, 3, or 4).

I have a series of hyperlinks in cells that display dates. I am looking for a formula that will reside in a different cell, that determines the latest (max) of these dates, and then will have a hyperlink that connects to the hyperlink of that cell.
That is, I would like to have hyperlink in a cell, and have it open the hyperlink of a different cell. The selection of that different cell is based on a determination as to whether the date displayed in that cell is the maximum value of a range of cells (each cell has different hyperlinks).
Thank you! in advance


Hello all,

I have a formula like below with a HYPERLINK function inside an IF statement.

=IF(A3="red",HYPERLINK("http://www.google.com","GOOGLE"),"no link")

When cell A3 = red, the link works perfectly. However, when A3 does not equal red, the cell with this formula still appears as if it is a hyperlink. I get the hyperlink cursor when I hover over the cell, and when I click the cell I get an error message stating that the address of the site is not valid.

Is there any way to get rid of this error? If I write the formula like below I don't get the error, but this doesn't give me the result that I want.

=IF(A3="red",HYPERLINK("http://www.google.com","GOOGLE"),"")

Thanks,
Rick

Excel 2003


Morning all
I'm trying to use a hyperlink based on a cell that has an indirect() ref to a worksheet which is named the same as the cell.
eg.
Index Sheet Cell Formula/Text
A1 Archer
B1 =INDIRECT("'"&A1&"'!A1")
C1 =HYPERLINK(B1)

now if the sheet 'Archer' Cell A1 had the text 'help' then the result of 'Index!B1 would be 'help' so I know the function is referring to the correct place in the workbook. How then do I get Index!C1 to hyperlink to Archer!A1

the reason for all this is that I want to create and navigate a workbook with 200+ tabs with an index using filters (ie. Index!ColA) then click on the result of the filter (Index!ColB) to jump to the correct tab (or use a lookup, either way is fine) so I want the hyperlink formula based so I don't have to Insert/Hyperlink each time (by the way I will be creating 4 of these so 1000 + hyperlinks).


I am addicted to the knowledge on this board...

What I currently have is a list of people that is created from references. Each person's name is also the name of a worksheet in the book. I want to take this name (output) and insert it into a hyperlink formula in the cell next to their name that would allow the user to quickly jump to their personal sheet.

I have tried this on a whim but it did not work...

=HYPERLINK("(D6)&!", (D6))

I was hoping that the value in cell D6 would be inserted and thus give me something like =HYPERLINK("Chris!", Chris) although I am not sure that is even the correct formula for linking to a worksheet.


Hi all,

I have a question about hyperlink function. All the answers I can find on the internet is like =hyperlink("http://www.test.com","My Link").

What I really need to do is we created a series of URL in one column and we tried to make the URL click-able.

For example, I have an URL in cell A1 and I want to have a hyperlink in cell B1. When I tried either =hyperlink("A1","My Link") or =hyperlink(A1,"My Link") and that did not work.

Dose anybody have any idea about how to do it?

Thanks!


Hi,

I'm trying to insert a Hyperlink into a cell. The length of the hyperlink,
is 322 characters. When I use the Insert Hyperlink menu, the hyperlink gets
cut off, leaving the link unusable. If I use the hyperlink function I get a
"Formula is Too Long" error.
Is there a way for this to work? I am using Excel 2002.

Thanks
Jason



I'm having trouble posting hyperlink inside an individual cell. I need to highlight some of the text within a cell and create a hyperlink to another folder. However, when i try to insert a hyperlink, the entire cell content is selected and becomes the link (not just the few lines of text i would like).

does anyone have a solution?

many thanks




I have a worksheet with about 1800 rows and multiple columns. In one particular column, the cell MAY contain a hyperlink. I accidentally removed the hyperlink style (by trying to get rid of a conditional formatting rule ) and now all the cells in the column have the same plain formatting whether they contain a hyperlink or not. Instead of formatting each cell individually, can I create a macro that will check if the cell contains a hyperlink and if so, apply the hyperlink style? How do I do this?
Thank you.

Is there a way to create a hyperlink that is dynamically linked to a cell value, in which the cell value points to a cell reference within a worksheet, and that when the value is changed can point to a different cell reference.

I am looking for a way to create a hyperlink without using Macros. I have a column (P) in a worksheet called Annual Review. Everything in column P is an automaticly generated Row number referencing the customers details on the other worksheet "MGP4" So if in P2 on "Annual Review" it says 43. I know that the Customers Details can be found on line 43 on worksheet "MGP4".

Im looking for a way to make it a hyperlink but as a formula so i can copy it down, there are several thousand entries on both worksheets.

If anyone can help me or has any questions i would be very grateful.

Thanks,
Harry


I made a spreadsheet with hyperlink formulas linking to other sheets in the same workbook. My boss is accessing the file via a Mac but the hyperlinks are dead. When clicked, nothing apparent happens.

The hyperlink is as follows:

HYPERLINK("#"&CELL("address",INDIRECT("'"&C10&"'!$b$8")) (The hyperlink takes the name in the adjacent cell and links to a tab with that name.)

I have it with an IF(ISERR()) formula because I wait until an adjacent cell is populated for it to appear.

Complete formula:

=IF(ISERR(IF(C10"",HYPERLINK("#"&CELL("address",INDIRECT("'"&C10&"'!$b$8")),C10),"")),"",IF(C10"",HYPERLINK("#"&CELL("address",INDIRECT("'"&C10&"'!$b$8")),C10),""))but the hyperlinks don't work. Any ideas?

Any ideas geniuses? :O)


Hi, I'm trying to find a way for a macro to open a hyperlink. the hyperlink is in cell b3. I need the marco read the hyperlink address each time it is run as the hyperlink may change.

Thanks


Hi all,

I'm asking the wider community of expert knowledge to help me crack a problem that a novice like me can't work out even after trawling the interwebby.

I have a workbook with two sheets. The first sheet has a lot of number data, the second has descriptions etc.

I want to hyperlink from a cell on sheet 1 (B2) that links to the info on sheet 2 (C2). So far so good, I've done this using the standard hyperlink and the formula hyperlink way and editing text display with ease.

What I can't work out is how to copy the hyperlink so that the row cell reference changes in context so that sheet1 B4 links to sheet2 C4 or B5 to C5 without editing every cell: the hyperlink/formula stays the same whether I copy+paste or drag paste.

Help would be appreciated in layman's terms. Thanks in advance.

S


I would like to use the hyperlink formula to link to a range of cells in the same sheet.

This should be simple and works when I press ctrl+K and enter A1:A2 for the cell reference; but not with the formula.

=hyperlink(A1:A2,"Link to A1:A2")

ALSO, if there's a way to hyperlink from one cell to multiple non-contiguous ranges that would be amazing!!! Something like pressing a hyperlinked cell A1 and selecting C1:C3 and E1:E3.

Any help is much appreciated...

Thanks,
Dan


I am trying to build a hyperlink useing various cells. Currently the formula looks like this

Quote:

=(L2&H10&L3&K14&L4&C1&L5&C1&L6&C4&L9)

The results of the formula can be displayed as text but as soon as I add "=HYPERLINK" it errors. Is there another method I can use to get this to run?

I was able to find a workaround regarding copying the hyperlink pasting it into word then pasting it into excel which allowed the hyperlink to exceed 255ch. When I modify the cell to the original formula the URL does not update and keeps going to that original page.


Hi,

I am new here so I apologize in advance if I am not posting or explaining this properly. I have scoured the web and cannot find the answer so hopefully someone here can help...

I am using the =hyperlink function as follows...

Code:

=HYPERLINK("http://"&A3&I19,"Click Here")


Now the "Click Here" shows up fine and the link works when I click it manually...

But I tried to get a macro to automatically click it....

Code:

Sub test()
 For Each Cell In Selection
    ' Check that the cell contains a hyperlink
      If Cell.Hyperlinks.Count > 0 Then
         Cell.Hyperlinks(1).Follow (True)
      End If
  Next Cell
End Sub


And the code only works if it is an actual typed in hyperlink and not the hyperlink function using the formula...

Hope I explained that right... any thoughts? Questions?

Thanks,

Henry