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

zaquin

New Member
Joined
Mar 22, 2016
Messages
4
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?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
this works for me

=HYPERLINK(CELL("address",INDEX(MyTbl,MATCH("Atlanta",MyTbl[TVSeries],0),4)),"Friendy name")
 
Upvote 0
I saved the workbook, closed it and re-opened it and now the link fails for me too :confused::confused:
- in fact every similar link fails

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

Later today I will test further and get back to you
-
 
Upvote 0
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 :oops:
Workbook renamed to include spaces in the name - the link failed :oops:

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......
 
Last edited:
Upvote 0
This works for me
=HYPERLINK("#" & CELL("address",INDEX(MyTbl,MATCH("Atlanta",MyTbl[TVSeries],0),4)),"Friendy name")
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top