Hyperlink same sheet "cannot open specified file" error

exsalad

New Member
Joined
Dec 3, 2008
Messages
12
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

exsalad

New Member
Joined
Dec 3, 2008
Messages
12
Yes. saved on the local drive. Error even after closing and reopening Excel.
 
Last edited:

exsalad

New Member
Joined
Dec 3, 2008
Messages
12

ADVERTISEMENT

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-type:personal; 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:p></o:p>
 

exsalad

New Member
Joined
Dec 3, 2008
Messages
12
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Dec 3, 2008
Messages
12
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Sure:

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

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,610
Members
414,080
Latest member
penguin23

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
Top