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?
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,744
Office Version
  1. 365
Platform
  1. Windows
this works for me

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

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,744
Office Version
  1. 365
Platform
  1. Windows
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
-
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,744
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,744
Office Version
  1. 365
Platform
  1. Windows
This works for me
=HYPERLINK("#" & CELL("address",INDEX(MyTbl,MATCH("Atlanta",MyTbl[TVSeries],0),4)),"Friendy name")
 

zaquin

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

Watch MrExcel Video

Forum statistics

Threads
1,108,973
Messages
5,525,984
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top