HYPERLINK Formula Dynamic vs Static Cell Referencing
Greetings and Happy 2016 to all,
For those who have experimented with hyperlinking locations (Creating a Table of Contents/Sitemap) within and across workbooks, the HYPERLINK Formula would have been a handful to figure out.
Here we explore some of its relevant syntax:
HYPERLINK(link_location, [friendly_name])
link_location (Necessary) - Destination/Precedent in "double quotes" or cell reference containing Destination/Precedent text string.
[friendly_name] (Optional) - Appearance of cell contents when out of Edit mode , uses link_location when unspecified, in "double quotes" or cell reference containing text string.
Summary of link_location syntax for 4 different scenarios depending on locations of HYPERLINK FORMULA and link_location.
Ignore "double quotes" if using cell reference containing text string, # hashtag represents same Workbook.
1) Same WorkSheet - "#CellAddress"
2) Same WorkBook, different WorkSheet - "#'SheetName'!CellAddress"
3) Different WorkBook in same Folder - "[WorkBookNameWithExtension]'SheetName'!CellAddress"
4) Different WorkBook in different Folder - "[Filepath\WorkBookNameWithExtension]'SheetName'!CellAddress"
Explanation and examples
While editing formula, Click to add Destination/Precedent Dynamic Cell References in Green
Dynamic Referencing - When dependant workbook is closed, precedant cell movements are NOT updated
Dynamic Referencing - When dependant and precedant workbooks are open, precedant cell movements ARE updated
Convert Dynamic Referencing to text string by clicking link_location on Formula tooltip, hit F9 on keyboard and Enter to allow linking of closed Precedent workbooks. Removes Dynamic References and will NOT update.
1) Same WorkSheet - "#CellAddress"
Syntax - Hashtag followed by Destination/Precedent CellAddress
Example (Text String) - =HYPERLINK("#DUO3","SameWS")
Example (Dynamic Referencing) - =HYPERLINK("#"&CELL("address",DUO3),"SameWS")
2) Same WorkBook, different WorkSheet - "#'SheetName'!CellAddress"
Syntax - Hashtag followed by 'SheetName' surrounded with single quotes followed by exclamation mark then Destination/Precedent CellAddress
Example (Text String) - =HYPERLINK("#'Sheet2'!DUO3","SameWB")
Example (Dynamic Referencing) - =HYPERLINK(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CELL("address",Sheet2!DUO3),"'",""),"]","]'"),"!","'!"),"SameWB")
3) Different WorkBook in same Folder - "[WorkBookNameWithExtension]'SheetName'!CellAddress"
Syntax - [WorkBookNameWithExtension] surrounded with square brackets followed by 'SheetName' surrounded with single quotes followed by exclamation mark then Destination/Precedent CellAddress
Example (Text String) - =HYPERLINK("[hyperlink.xlsx]'Sheet2'!DUO3","SameFolder")
Example (Dynamic Referencing) - =HYPERLINK(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CELL("address",[hyperlink.xlsx]Sheet2!DUO3),"'",""),"]","]'"),"!","'!"),"SameFolder") Dynamic Referencing works only when workbook is opened
4) Different WorkBook in different Folder - "[Filepath\WorkBookNameWithExtension]'SheetName'!CellAddress"
Syntax - [Filepath\WorkBookNameWithExtension] surrounded with square brackets followed by 'SheetName' surrounded with single quotes followed by exclamation mark then Destination/Precedent CellAddress
Example (Text String) - =HYPERLINK("[C:\Users\User\Desktop\hyperlink.xlsx]'Sheet2'!DUO3","DiffFolder")
Example (Dynamic Referencing) - =HYPERLINK("["&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CELL("filename",'[hyperlink.xlsx]Sheet 2'!DUO3),"'",""),"]","]'"),"[","")&"'"&REPLACE(CELL("address",'[hyperlink.xlsx]Sheet 2'!DUO3),1,SEARCH("!",CELL("address",'[hyperlink.xlsx]Sheet 2'!DUO3))-1,""),"DiffFolder") Dynamic Referencing works only when workbook is opened
As is obvious from above, Dynamic Referencing in HYPERLINK Formulas can be quite a challenge due to the different syntax required compared to syntax provided by the CELL Formula. Share with us in the comments below how you manage HYPERLINK Formulas' syntax requirements and stay HYPER for 2016!
Comentarios