Free Microsoft Excel 2013 Quick Reference

Hyperlink functions Results

Hello, friends.
Is any way to change hyperlink function from absolute to relative referencing.
The reason for my request: I have *xls file with 1000 rows. Each row points to some file. When I burn this folder to disk , hiperlink doesn't work.

Thanks in advance.


I have the following problem:
There are several Sheets in my Workbook. One Sheet (overview) has cells with contents like "breast", "colon", "lung", working as a table of contents.
Furthermore, I have corresponding Sheets named "breast", "colon", "lung", where the details are situated.
Now, when the user clicks on a cell named "breast" in the overview Sheet, he should be linked to the relating Cell in the "Breast" - Sheet.
How can the Excel-Hyperlink - Function be improved so that there is also the possibility to set links to certain cells, not only to certain Sheets?



I am trying to come up with formula to use HYPERLINK function.

IF A2="YES" THEN B2=HYPERLINK("c:temptest.file")

I need cell B2 to show hyperlink based on the value of cell A2 (YES/NO).

Is this possible?

Thank you,


I am trying to hyperlink a cell to a file which contains a "#" in its name e.g. "AngloGold #1.xls"

Excel converts this to "AngloGold - 1.xls"

I have tried "AngloGold%20%231.xls", but that doesn't work.

A solution or even just an explanation as to *why* Excel does this will be appreciated.



I need an excel function with 'hyperlink function' that also links file path including file name from one cell to file extension in another cell.

Is it possible to put this all in one cell?

Is there anyway to reference to a cell in the current workbook using the HYPERLINK function.

I would like to be able to create a dynamic link e.g.

=HYPERLINK(INDIRECT("L"&SUM(1+Variable)),"Dynamic Link")



I am having difficulty with the Hyperlinks function. I have a button which creates a new row and is then supposed to add a hyperlink to the last sheet in the workbook but i keep getting an error. My code is as follows:

Private Sub AddEntry_Click()

'Copy and paste the last row to the 3rd to last available row

Dim Lastrow As Long, Newentry As Variant
Lastrow = ActiveSheet.UsedRange.Rows.Count
ActiveSheet.Rows(Lastrow - 3).Select
Selection.Copy Destination:=Range("A" & Lastrow - 2)
Application.CutCopyMode = False
ActiveSheet.Rows(Lastrow - 1).Insert

'Ask for new entry name and paste to new row

Newentry = InputBox("Please enter the name of the new entry")
Range("B" & Lastrow - 2).Value = Newentry

'Insert new worksheet

Dim Countsheets As Variant
Countsheets = Sheets.Count
Sheets.Add.Name = Countsheets
Sheets(1).Move After:=Sheets(Countsheets + 1)

MsgBox "There are now " & Countsheets & " worksheets in this workbook"

'Copy contents of last worksheet to new worksheet

Countsheets = Sheets.Count
Sheets(Countsheets - 1).Range("A:Z").Copy
Sheets(Countsheets).Range("A:Z").PasteSpecial xlPasteAll
Sheets(Countsheets).Range("B1").Value = Newentry

'Add hyperlink to new entry linked to new sheet

Dim Thetarget As String
Countsheets = Sheets.Count
Thetarget = "Sheet" & Countsheets & "!A1"

Range("B" & Lastrow - 2).Hyperlinks.Add Anchor:=Newentry, _
Address:="", _
SubAddress:=Thetarget, _

End Sub
Any help with the hyperlinks part of the code would be greatly appreciated as I have been trying to solve this for some days now.


I am creating a hierarchy chart and would like to add a hyperlink to each of the levels of the chart. The Hyperlink functionality is disabled when I am editing each of the "levels" or boxes of heirarchy chart. When creating the same chart in 2007 Powerpoint the Hyperlink functionality will work. Any suggestions?

I use the Hyperlink function to create a hyperlink from the data entered in to cells on a worksheet. Next I use the code below to copy the desired cells from the worksheet "Doc Library Publishing Request" to the appropriate columns in "Publication Request Log". My problem is that the Hyper link I created in cell C10 on the request isn't a hyperlink when it gets to the first column of the log. My guess is that the format doesn't contain http: which tells Excel to make it a hyperlink. Is ther an easy way to adjust my macro to make the data pasted in the first column a hyperlink? I found the attached code on the web and worked with it a little to get it to do more or less what I wanted, but I don't really understand all of it.

Thank you for any help

Sub UpdateLogWorksheet()
Dim requestLog As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myRng As Range
Dim myCopy As String
Dim myCell As Range

'cells to copy from Input sheet - some contain formulas
myCopy = "C10,C6,F4,C12,F6,F10,F12"
Set inputWks = Worksheets("Doc Library Publishing Request")
Set requestLog = Worksheets("Publication Request Log")
With requestLog
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
Set myRng = .Range(myCopy)
If Application.CountA(myRng)  myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With requestLog
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Cells(nextRow, "B").Value = Application.UserName
oCol = 1
For Each myCell In myRng.Cells
requestLog.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With
Worksheets("Publication Request Log").Activate
End Sub

Hi i was wondering if anyone could help me. On my excel sheet i want to insert a hyperlink , when i go to insert hyperlink i want to default look in a specific folder everytime. I only want to have this available for this one particular workbook.

Would it be possible to do a macro or be able to edit the insert hyperlink function for this one particular workbook.

Many Thanks

Hey Guys,

Thanks in advance for your help on this one

I've got a list about 1500 records long that I want to hyperlink to matching records on the following page sheet.

I.E. Customer A on sheet "Total Sales" needs to hyperlink to Customer A on sheet "By Product".

I tried the following function

=MID(CELL("filename",'By Product'!$A$1),FIND("[",CELL("filename",'By Product'!$A$1)),256)&"!"

as named formula Psheet2 in the following formula, nested in a hyperlink formula.

=HYPERLINK(WBsheet2 & ADDRESS(MATCH(B17,'By Product'!B:B,0),(MATCH(B17,'By Product'!B:B,0))))

where B17 is the location of the customer name, and column B in By Product contains the matching Customer name

I didn't enter a friendly name at the end, but I dont think that should matter.

When I try this I get an error message: Reference Not Valid. I think it has something to do with the fact that both match functions return the same coordinates, but I'm not sure what should go in there.

Thee other thing I tried was using a match function to find the row number of the matching customer, (I know the column is B) and then add that into a hyperlink function, like so...

=HYPERLINK('By Product'!B112)

When I do that, it returns a hyperlink with the name of the proper customer, but when I open the link, I get an error message, "Cannot open the specified file.

I tried to add the file name with the original named formula, , and got something like this...

=HYPERLINK(MID(CELL("filename",'By Product'!$A$1),FIND("[",CELL("filename",'By Product'!$A$1)),256)&"!"&"J45")

with the cell I want to link to typed in J45 (i.e. B101 is in J 45)

but I keep getting reference is not valid messages.

Any Ideas?

I have a spreadsheet with a significant number of tool details on a
number of sheets (broken down by market type). I have a front page
that summarises the other sheets, but what I want to add to the front
page is a search function that then enables a hyperlink function to
take the user to the location in the spreadsheet where that tool
number's details are located.
I can use the VLOOKUP function to search for the tool number and verify
it's existance, but I dont know how to use the functions or write a
macro or VBA to hyperlink to that tool number. Any suggestions?

Brisebear's Profile:
View this thread:


My SMS-provider has an API where it's possible to send an SMS by specifying
a URL. I've made an Excel-sheet that has all relevant fields concatenated
and put into an HYPERLINK function. When I click the hyperlink the default
browser is called with the URL. The browser returns with a page with text
"Message sent: ID 1234567"

The problem though is that _two_ sms are sent. I can see in the sms log
that it is the ID of the second sms that is returned in the browser.

It seems like Excel is performing its own check before calling the browser.
Try making a cell with a referance to a server that does not exist (e.g.
'=hyperlink( and click the link. Excel then
returns a message box with the text "Unable to open Cannot locate the Internet server or proxy

If you make a real link Excel tries the link and if its valid calls the
browser with the same link. The sms-api believes that this is two distinct
commands and sends two identical sms to the same user.

I would like to know if there is a way around this problem (exept make a
server side workaround). Is this a bug or a feature in Excel?


Thomas, Norway

I have two simple workbooks open. One I have no problem in creating
hyperlinks to email - the other has the hyperlink function "greyed out" both
from the Insert tab as well trying to create from right click. (I don't know
if this is relevant but when I go file/properties/summary on the non
hyperlinked workbook, the summary details are also greyed out whereas on the
active hyperlinked workbook those details on the summary tab are active.)
I've searched everywhere as to why/how but no joy (this includes the website
phil b

How do you keep the hyperlink URL active in a dropdown list. On one
worksheet I have the following in a cell
"JUNOS 101 White Paper") . I have named the 4 cells test. On another
worksheet in the workbook I created a validation list and enter the name
=test. But when I use the drop-down list ithe hyperlink function does not work


I have a worksheet with addresses of the clients, so each row represent
one client (Name, Title;Street+no, ZIP, city ...)
I would need to put on the first column a special hyperlink, which will
be composed from the values of next columns.

Like :
=HYPERLINK(""&";"&C12&";"&IF(D12=$ C$5;0;IF(D12=$C$6;1;IF(D12=$C$7;2;3)))&";"&E12&";" &F12&";"&G12;"Open
in browser")

So the hyperlink would look like;John Dick;0;Lombard street
1204;10304;Zurich ...

But the hyperlink does not work, when in one or more of the cells is a
value containing space - in this example the name and street. I've
tried to replace the spaces with %20, but still the same. Simply Excel
will not open the browser, nor display an error message in those
However, if I remove the space, it works.

This problem occurs only in Excel 2003, in XP it works..



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.


I have a requirement to dynamically build/create a hyperlink in a workbook
which would allow the user to quickly move between worksheets. ALL
informataion is contained within the workbook so there is no need (or
desire) to have any external links. The workbook would be laid out as


Project 1
Project 2
Project 3

Each of the Project worksheets would have a project title located in cell I8

On the summary worksheet, each row would provide summary information pulled
directly off the Project worksheet. The worksheet name would be place in
column N therefore all pulls of information from that project would be done
referencing the worksheet name found in column N.

The requirement would be to dynamically build a hyperlink from the summary
worksheet to the respective project worksheet with the link being
effectively the title of the project (pulled for the project worksheet) and
the linke place in column B.

I have tried the HYPERLINK function as the help text implies this will
work - any suggestions?? - Here is information from the Help Text:

You can create hyperlinks within a worksheet to jump from one cell to
another cell. For example, if the active worksheet is the sheet named June
in the workbook named Budget, the following formula creates a hyperlink to
cell E56. The link text itself is the value in cell E56.

=HYPERLINK("[Budget]June!E56", E56)

To jump to a different sheet in the same workbook, change the name of the
sheet in the link. In the previous example, to create a link to cell E56 on
the September sheet, change the word "June" to "September."

I am running excel 2003. When I create a hyperlink to a tif image on my
server (intranet) the hyperlink works. Once I save the document the
hyperlink will no longer work. Also, there are some hyperlinks that were
previously added to the worksheet (.tif) that still work even after the
document is saved. I have compared the two address of the non-working and
the working hyperlinks and they are identical. Any thoughts??


I'm running Excel 2003. I have a list of values in one sheet (call it 1),
several worksheets of which each contains a unique status report and another
sheet (call it 2) that contains values from sheet 1 that also contains a
hyperlink to the unique status report sheet. The values in sheet 1 change a
couple of times a week and contain up to 700 rows of data. What I cannot
figure out is how to compare the values in sheet 1 against the values in
sheet 2 and if there is a match, then copy the hyperlink to the status report
from sheet 2 to sheet 1. I can do a vlookup to compare the values but I
cannot get the hyperlink function to work. I prefer not to use a macro if at
all possible unless it can be setup to only run from sheet 1 and only look at
sheet 2.

Any suggestions will be greatly appreciated!


No luck finding an answer? You could always try Google.