hyperlink formula using CELL("address"...)

[zaquin]

Hello. I've had a lot of luck using the hyperlink formula but am stumped by this one.
Here's the path to the workbook:
C:\Users\MyName\Documents\ExcelFolder\MyWorkbook.xlsb > MySheet > MyTbl and in column D of MyTbl is the field TVSeries. In cell D17 is "Atlanta" without quotes.
I'm trying to use the hyperlink formula to link to that cell from a different worksheet in the same workbook.
First, I have the CELL/address formula:
=CELL("address",INDEX(MyTbl,MATCH("Atlanta",MyTbl[TVSeries],0),4))
which results in:
'[MyWorkbook.xlsb]MyTbl'!$D$17
Then I use Substitute to get the correct string for the cell:
=SUBSTITUTE(CELL("address",INDEX(MyTbl,MATCH("Atlanta",MyTbl[TVSeries],0),4)),"'[MyWorkbook.xlsb]",""""&"#'")&""""
which gets me to:
"#'MyTbl'!$D$17" which is what I want for the link_location.
But if I if I enter the Substitute formula for the link_location I get the "Cannot open specified file." error.
Anyone have any suggestions? This works: =HYPERLINK("#'MyTbl'!$D$17","FriendlyName") so I'm stumped why the Substitute formula result doesn't. The Hyperlink formula reads the link_location as a string, right?

 

[Yongle]

this works for me

=HYPERLINK(CELL("address",INDEX(MyTbl,MATCH("Atlanta",MyTbl[TVSeries],0),4)),"Friendy name")

 

[zaquin]

this works for me

=HYPERLINK(CELL("address",INDEX(MyTbl,MATCH("Atlanta",MyTbl[TVSeries],0),4)),"Friendy name")

Thank you Yongle. Not sure why it won't work for me.

 

[Yongle]

I saved the workbook, closed it and re-opened it and now the link fails for me too
- in fact every similar link fails

The link works before I save a workbook, but then fails when re-opened after a save

Later today I will test further and get back to you
-

 

[Yongle]

I have made some progress

This works
New workbook with name "NameWithoutSpaces.xlsx" \ sheet named "SheetNameWithoutSpaces" with table MyTbl \ and this formula:
=HYPERLINK(CELL("address",INDEX(MyTbl,MATCH("Atlanta",MyTbl[TVSeries],0),4)),"Friendy name")
I tested that - the link works

This makes links fails
Sheet renamed to include spaces in the name - the link failed
Workbook renamed to include spaces in the name - the link failed

Spaces in names are bad!
So the problem is due to spaces being included in EITHER the name of the workbook OR the name of the sheet containing the table (OR BOTH!)

There must be a solution to this somewhere
- but at least you have a workaround in the meantime!

I will investigate further......

 

[Yongle]

This works for me
=HYPERLINK("#" & CELL("address",INDEX(MyTbl,MATCH("Atlanta",MyTbl[TVSeries],0),4)),"Friendy name")

 

[zaquin]

This works for me
=HYPERLINK("#" & CELL("address",INDEX(MyTbl,MATCH("Atlanta",MyTbl[TVSeries],0),4)),"Friendy name")

YES! This works for me too Yongle. It seems that the single quote I put in was throwing it off for some reason. I believe a single quote is needed for worksheets with a space in the name, but I guess there's something in my formula that makes the inverse true, the single quote must be omitted if there is no space in the name? Or maybe the single quote I was adding was in fact added double single quotes?
Thanks all!