Using INDIRECT inside HYPERLINK in excel

3
votes

I am trying to create a hyperlink in Excel that references a cell on my worksheet, to get the sheet name.

Here is the working formula, with a static value.

=HYPERLINK("[\\xxxfs01\xxxxxxxx\IT\Monthly Reporting\Data\Computers Report for xxxDMZWSUS01.xls]Sheet1!A1","CLICK HERE")

When I use INDIRECT to reference a dynamic value, the cell just shows a value of #VALUE!

Here is what I have tried.

=HYPERLINK(INDIRECT("""&[\\xxxfs01\STS-Defence\IT\Monthly Reporting\Data\Computers Report for xxxDMZWSUS01.xls]"&[@Sheet]&"!A1&""","CLICK HERE")) 

=HYPERLINK(INDIRECT(CHAR(34)&"[\\xxxfs01\STS-Defence\IT\Monthly Reporting\Data\Computers Report for xxxDMZWSUS01.xls]"&"Sheet1"&"!A1"&CHAR(34),"CLICK HERE"))

Can anyone help with the correct syntax?

2
excelhyperlinkexcel-indirect
It is not clear what you are trying to achieve. What in the path "[\\xxxfs01\xxxxxxxx\IT\Monthly Reporting\Data\Computers Report for xxxDMZWSUS01.xls]Sheet1!A1" shall be dynamic and where are the dynamic parts stored? Your tried examples cannot work. But, as said, not exactly clear what they should do. – Axel Richter

2 Answers

1
votes

You don't need INDIRECT to put together a Hyperlink location (INDIRECT is specifically intended to allow you to change the cell being reference in a formula, and that's not what I think you're trying to do). I'm listing steps below, because I think it might be a tiny bit confusing what you are trying to do, so this gives you a chance to see if I understood your problem correctly.

  1. You are in Worksheet1.xlsx, Sheet1
  2. In cell B2 of Sheet1, you want to enter the name of a worksheet from a different file: Computers Report for xxxDMZWSUS01.xls
  3. In cell B3 of Sheet1, you have a hyperlink to go to cell A1 of the worksheet named in cell B2
  4. The formula in B3 should be:

=HYPERLINK("[\\xxxfs01\xxxxxxxx\IT\Monthly Reporting\Data\Computers Report for xxxDMZWSUS01.xls]"&B2&"!A1","CLICK HERE")

0
votes

INDIRECT Worksheet Function

Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

An example of using INDIRECT and HYPERLINK is:

=IF(TRIM(A2)="","",IF(ISERROR(INDIRECT("'" & A2 & "'!$A$1")),"missing", 
    HYPERLINK("#" & "'" & A2 & "'!$A$1",INDIRECT("'" & A2 & "'!$B$2")) ))

Reference is http://dmcritchie.mvps.org/excel/indirect.htm