Hyperlink formula not working despite displaying correct address

Question

I am using the formula:

=HYPERLINK(CELL("address",INDIRECT("'"&C38&"'!A1")))

Where C38 contains

0999-0165

and I have a sheet called 0999-0165. There are no leading or trailing characters, and since the name in C38 is generated by the sheetname there is no error between 0 and O.

The sheet has also been saved to my desktop as dtest.xlsx

When I click on the formula, I get the following error:

MS Help indicates that I should be going down the right path here:

Where did I go wrong

What do I need to do to adjust this formula to make it work so that I can simply copy the formula up or down to create other links for other reference sheetnames in column C?

I also did the work around from this question but it did not seem to help.

Muddying the water

When I use a direct cell reference and drop the indirect part for testing purposes to make it look the same as the MS Help recommendation, I wind up with the same error:

=HYPERLINK(CELL("address",'0999-0165'!A1))

And BTW, the above formula winds up displaying the same as the Indirect one. Complete with adding the $

Answer 1

Here's a long workaround to this, if you don't want to hard-code the workbook name. Get the entire filename with CELL("filename",A1) and then extract just the workbook name and the enclosing brackets using MID and FIND.

=HYPERLINK(MID(CELL("filename",A1),FIND("[",CELL("filename",A1)),FIND("]", CELL("filename",A1))-FIND("[",CELL("filename",A1))+1)&"'"&C38&"'!$A$1")

Or slightly more simple:

=HYPERLINK(SUBSTITUTE(SUBSTITUTE(CELL("address",INDIRECT("'"&C38&"'!A1")),"'[","["),"]","]'"))