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


Free Excel Forum

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 Video Tutorials

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







I had a browse around the forum for an answer to my question, but without any joy. Hopefully somebody can help me out...

I'm hyperlinking within an Excel spreadsheet; from a cell in one worksheet to a cell in another. This is working fine. However, when I add/remove a row/column my hyperlinks get screwed up.

For example, I'm linking from cell A2 in SheetA to cell B2 in SheetB.
If I add a row in SheetB, the cell I'm referencing now becomes cell C2.
However, the hyperlink does not become C2; it stays as B2.
Therefore my hyperlink is referencing the wrong cell.

There must be a way to keep the links when I add/delete a row.

Any ideas?

Thanks,

Tim


Here I am again with another hyperlink problem. I think I might have a hyperlink addiction.

The formula below should lead me to a worksheet that contains the named range MAXSHEET. However, when I click on the link I get "Reference is not valid". I know the index formula is working fine, it's returning the correct result. Why can't I hyperlink to the cell that contains that result?

=HYPERLINK("#"&INDEX(MAXSHEET,3,6,1),"LINK")

Thank you in advance.


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


I'm using the formula below to automatically select the hyperlink location.

=HYPERLINK("#"&VLOOKUP(G27,EXERCISE_1LU,2,FALSE),"1B")

It works great with named ranges, but I want to get crazy and have the hyperlink result be an actual web address versus a defined range name. The result I'm getting is "Reference is not Valid"

I've used both the http:// prefix and only www. prefix. Neither worked. Any help would be greatly appreciated.


Sheet 1, cell A1 has the hyperlink.

Hyperlink details are ...
Type the "cell reference" is C1.
Select a place in this document: Cell reference 2 (sheet 2).

Formula required for "cell reference". I want the C1 to change on a daily basis, to the next row without having to edit the hyperlink.


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!


Hi everyone,

I'm trying to figure out a formula (preferably formula, if not possible, then custom VBA formula) that determines whether the current cell is a hyperlink.

For example, is there a formula that looks like this?

=IF(ISHYPERLINK(A1),"This cell is a hyperlink","This cell is not a hyperlink")

Where A1 is the cell being tested.

Please let me know! I've searched the web and the forums for this without answer

Thank you!


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

I recieved help with this from the Board and it was great. As with all things it posed new questions.
Now that I can change the Active Sheet name automatically I want to be able to Hyperlink it to another sheet. What I do is set a hyperlink from a "Table of Contents" page. The problem now is that if the name of the page changes, the Hyperlink reference will no loger reference the page. Is there a formula that will change as the Active sheet name changes?
ie.
Cell $C$7 will change from Last Name, First name to another Last name/ First name. This will change the Reference and the hyperlink will no longer work. Is there a way I can have the Hyperlink ALWAYS reference this page, no matter what the name change?
Thanks in advance!!

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
Dim Rng1 As Range

If Target.Address = "$C$7" Then
ActiveSheet.Name = Left(Target.Value, 12)
End Sub


Hello,

I need to have a solution for the following excel requirement.

Assume that C5 has "some_val".
i need to look in the whole column of A for the cell value of "some_val" and i have to create a hyperlink to the matching cell.
(Assume that A15 has "some_val", then hyperlink in C5 should link to A15 automatically)

This process has to be automated as i have about >1000 cells. Hope i'm clear with my requirement!

Looking for some formula/vb script


Thanks in advance




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



The following formula works:

=IF(AND($B5=$B4,$B4=$B3,$N5>=1),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B4&"("&SUM(O$4,$N4,$N3)&")"&".JPG","("&SUM(O$4,$N4,$N3)&")"),IF(AND($B5=$B4,$N5>=1),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B4&"("&SUM(O$4,$N4)&")"&".JPG","("&SUM(O$4,$N4)&")"),IF($N5>=1,HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B5&"(1).JPG","(1)"),"")))

All this formula says is that If I have a 1, 2, 3... 12 in this cell, then create a hyperlink ending in "(?)", where ? is a number depending on what I put in (either a 1, 2, 3... 12).

What I did now is rather than manually inputting in this 1, 2, 3... 12, I have it reference Sheet1!J2.
Sheet1!J2 will give me a number between 1-20 or a blank, depending on what the formula gives me.
The formula on Sheet1!J2 is =IF(K2="","",(MATCH(TRUE,INDEX(K2:X2="",0),0)-1)/2 )

So I will get my number, and it will create the right hyperlink.... but when the cell is blank, it still creates a hyperlink, although the cell is blank.

Why?
Where does my current long formula need to say: If cell is blank, don't create the hyperlink? It didn't do it before, but before i also manually input a number.

I have an Excel document where I want to put text and a hyperlink in the same cell. . . . it appears to make all the words a hyperlink rather than just the hyperlink itself . . . do you have a solution?


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




Hello,

I am not sure if hyperlink is the right feature for what i am looking!
I have a big sheet, with certain explanations. When i scroll down the sheet i will have to refer to some rows above. So i create a hyperlink to a cell on that row.
Once i am done reading that row, i want to go back to the row from where i came. But this time i cant hyperlink, because there could be multiple rows below which would refer to same reference.

Hope i have made my question clear, waiting for the advises.

Regards, Hani

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).

Greetings, I am using excel 2003 to create a pictorial directory for our church. On one worksheet I have ~200 pictures. On 13 other worksheets I have all of the addresses and such layed out. On a whim I started hyerplinking the pictures so that if you clicked on the persons picture you were taken right to their info - pretty straightforward stuff. But the thought just occured to me that if I add a new address here or there, all of the hyperlinks will need to be updated, or any of the ones after the new one anyway. I wonder if you can code the hyperlink to look for a cell value instead of a cell reference i.e. "Johnson" instead of "C4". I know that this is easy to do with the "=HYPERLINK" formula but not so sure about the object hyperlink. I looked through the message board and perused the excel help and did not really see anything. Anybody have any ideas? Thanks, RB


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


I have a spreadsheet where I use columns a, b, & c to create a hyperlink.

Column in D2 is =hyperlink(a2&b2&".htm",c2).

This works fine. What I want is a worksheet that just has D2 in it with the associated hyperlink, so it would appear that I typed in c2, and did an insert hyperlink.

Then I would not have to have columns a, b, c. I know that I can hide those columns, but is there a way to just copy the contents of my resulting cell d to a new workbook?


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).