Using INDIRECT inside HYPERLINK in excel
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?
Answer
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.
- You are in Worksheet1.xlsx, Sheet1
- In cell B2 of Sheet1, you want to enter the name of a worksheet from a different file: Computers Report for xxxDMZWSUS01.xls
- In cell B3 of Sheet1, you have a hyperlink to go to cell A1 of the worksheet named in cell B2
- The formula in B3 should be:
=HYPERLINK("[\\xxxfs01\xxxxxxxx\IT\Monthly Reporting\Data\Computers Report for xxxDMZWSUS01.xls]"&B2&"!A1","CLICK HERE")