Hyperlink same sheet "cannot open specified file" error

[exsalad]

This HAS to be a simple fix.... Could not find a string with this solution in it yet.

Trying to set up a hyperlink to reference another location in the same sheet.
Used the example in Excel as a model to set this up so placed this formula in cell B1:
=hyperlink([Book1]Sheet1!a1,"LINK")
this defaults to =hyperlink(Sheet1!a1,"LINK") since Book1 is the current wb.
clicking on "link" in cell B1 produces "Cannot open specified file" error.
I need the hyperlink to be dynamic, so I can't use the CTRL+K hyperlink function.

Thanks in advance for any help

 

[Andrew Poulsom]

Have you saved the workbook?

 

[exsalad]

Yes. saved on the local drive. Error even after closing and reopening Excel.

 

[Andrew Poulsom]

Should it be?

=HYPERLINK("[Book1.xls]Sheet1!A1","LINK")

 

[exsalad]

That's it! ".xls" is required, but not is not included the Excel example.<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5Cdavidc%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Font Definitions */ @font-face {font-family:Tahoma; panose-1:2 11 6 4 3 5 4 4 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:1627421319 -2147483648 8 0 66047 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} p {mso-margin-top-alt:auto; margin-right:0in; mso-margin-bottom-alt:auto; margin-left:0in; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} code {font-family:"Courier New"; mso-ascii-font-family:"Courier New"; mso-fareast-font-family:"Times New Roman"; mso-hansi-font-family:"Courier New"; mso-bidi-font-family:"Courier New";} span.EmailStyle15 {mso-style-typeersonal; mso-style-noshow:yes; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt; font-family:Tahoma; mso-ascii-font-family:Tahoma; mso-hansi-font-family:Tahoma; color:windowtext; font-weight:normal; font-style:normal; text-decoration:none; text-underline:none; text-decoration:none; text-line-through:none;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> <code>=HYPERLINK("[Budget]June!E56", E56)</code>


Thanks!
<code></code><o></o>

 

[exsalad]

How about if the workbook is set up as a "template" and the name changes when saved. Will this update automatically? If not, would you suggest a solution?

 

[Andrew Poulsom]

You need the extension if "Hide extensions for known file types" is unchecked on the Windows Explorer|Folder Options|View tab. Personally, I always include it, because it is safe to do so even if that option is checked.

 

[Andrew Poulsom]

How about if the workbook is set up as a "template" and the name changes when saved. Will this update automatically? If not, would you suggest a solution?

To make it change when the name of the workbook changes:

=HYPERLINK(MID(CELL("FileName",A1),FIND("[",CELL("FileName",A1)),255)&"!A1","LINK")

 

[exsalad]

OK, great! That works. Is there any way to structure this differently so that A1 is not a fixed reference? The target application is on sets of paired sheets with many hyperlinks between them. To expedite the setup, it would be advantageous if the reference to A1 would update relative to the location of the pasted cell. e.g if the formula was copied from B1 to B4, then the hyperlink reference would update from A1 to A4.

 

[Andrew Poulsom]

Sure:

=HYPERLINK(MID(CELL("FileName",$A$1),FIND("[",CELL("FileName",$A$1)),255)&"!"&CELL("address",A1))