Selina: Can you hyperlink from Word to a specific sheet and cell in Excel?
Yes! If you are careful with the hyperlink format.
One way:
C:\FolderName\FileName.xlsx#'Sheet1'!R99
or, you can use a named range:
C:\FolderName\FileName.xlsx#JumpHere
From the outtake: To prevent the annoying message, you can tell Word
that the folder containing the Excel file is a trusted location.
You have to exit Word and re-start for the message to go away.
Title Card Photo Credit: geralt / pixabay
Yes! If you are careful with the hyperlink format.
One way:
C:\FolderName\FileName.xlsx#'Sheet1'!R99
or, you can use a named range:
C:\FolderName\FileName.xlsx#JumpHere
From the outtake: To prevent the annoying message, you can tell Word
that the folder containing the Excel file is a trusted location.
You have to exit Word and re-start for the message to go away.
Title Card Photo Credit: geralt / pixabay
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2182: Can Word Hyperlink to a Specific Sheet and Cell in an Excel File?
Hey, welcome back to MrExcel netcast, I'm Bill Jelen.
Today, a question from Selina that I had no clue-- I've never done this before-- and I'm happy that I've figured it out, and I'm sharing it so that way you will know and also 2 years from now, when I have no clue how I ever did this, I can come back and find it as well.
Alright, so here's what we have.
We have a file: Podcast2182.xlsm.
It is saved in a folder.
Now, if you don't know what folder it is, I'm going to press Alt+F11; I'm going to press Ctrl+G for the Immediate window; I'm going to type "? Thisworkbook.FullName" and press Enter; and it's going to give me this thing here.
That's my full path and file name.
So, if I just want to link to the file, not to a specific place in the file, we would come here to Word-- I just copied that, by the way-- and select what we want the hyperlink to be.
I did a bad job of selecting that; MrWord.com could tell me a better way to select.
Alright, Ctrl+K-- and the address, paste, is just that.
Simple.
Alright, but that's not what Selina wants to do.
Selina wants to link to a specific sheet and cell address.
Alright.
So, here, I've already built this.
What we're going to do is we're going to put the whole path and file name like before, then a pound sign or hash sign [ # ], and then the sheet name in apostrophes, even if it's only a single sheet [ name ]; close apostrophe; exclamation point; R99.
Now, take that, Ctrl+C. Now, you've got to be really careful.
I screwed this up at first.
Putting xlsx there, make sure the extensions are right.
Make sure all the path is right, and so on.
So, let's come back to Word, and we're going to go to a specific sheet and cell; Ctrl+K; paste; alright, so, the whole thing, C:\FolderName\FileName.xlsx#'SheetName'!R99.
Alright, but, hey, my recommendation-- don't worry about that.
Let's just do this.
Let's say that we want to come here and jump to this cell.
What I'm going to do is I'm going to select that cell.
I'm going to name it.
I'm going to give it a name: "Jumptome," like that.
Or, how about "Jumphere"?
"Jumphere”-- that's a great name.
Press enter, like that, and we'll go back up to A1.
Here, we'll be at A1, and we'll save this in the title card.
Save; File; Close; come back to Word.
And, for a jump to a Named Range, I think this one is even easier: Ctrl+K, alright, and in this one, we just put the whole workbook name path, workbook name path, pound sign, and the name of the named range (C:\FolderName\FileName.xlsx#NamedRange) like that.
Click OK.
Alright, so, we're in Word.
The first hyperlink is just going to open the file.
Ctrl+click.
They warn me; I'm sure I could get around this by saving it in a trusted folder.
And, they open Excel just at the exact same spot where I had it before.
File; close; and then try this one more linking to a specific sheet the question sheet R99.
Ctrl+click, and they go to the question sheet and scroll to R99.
Now, they didn't scroll so that way R99 was in the top left-hand corner cell, but at least it's in the visible window.
Alright-- File; close; and then the last one, to a Named Range-- easier to set up-- you don't have to worry about the apostrophes-- Ctrl+click; yes; and it jumps to the report sheet "Jumphere" for test 1.
For me, I'm going to say that's working.
I'm sure if you had hoped it had jumped to this spot, like that, you're going to say that this is not working.
But, that's the way life goes.
To Microsoft, this is working.
Well, hey, for tips like this tip-- 617 Excel Mysteries Solved-- check out this book: Power Excel with MrExcel.
The 2017 edition is the latest version, even though we're already in 2018.
The next time I'll update this will be in 2019, so you're safe to buy it now.
Alright, recap-- Selina wants to know if you can link from Word to a specific sheet and cell in Excel.
Yes.
If you build the link carefully enough.
The format is C:\FolderName\FileName.xlsx#'SheetName'!A1-- or, whatever cell you want to go to.
Or, easier, if you set up a Named Range, C:\FolderName\FileName.xlsx#NamedRange.
Well, hey, I want to thank Selina for sending the question in, and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Alright, so, hey, let's just see if we can get rid of that irritating message.
We go to File; Options; (I'm in Word for this-- probably could do it from either side); Trust Center; Trust Center Settings; Trusted Locations; I'm going to Add a New Location; type the location there; click OK; click OK; super annoying-- you have to close Word; reopen Word; and then the trusted locations are updated.
Now when we click, Ctrl+click, it will not hassle you and ask you to go to a potentially untrusted location.
Hey, welcome back to MrExcel netcast, I'm Bill Jelen.
Today, a question from Selina that I had no clue-- I've never done this before-- and I'm happy that I've figured it out, and I'm sharing it so that way you will know and also 2 years from now, when I have no clue how I ever did this, I can come back and find it as well.
Alright, so here's what we have.
We have a file: Podcast2182.xlsm.
It is saved in a folder.
Now, if you don't know what folder it is, I'm going to press Alt+F11; I'm going to press Ctrl+G for the Immediate window; I'm going to type "? Thisworkbook.FullName" and press Enter; and it's going to give me this thing here.
That's my full path and file name.
So, if I just want to link to the file, not to a specific place in the file, we would come here to Word-- I just copied that, by the way-- and select what we want the hyperlink to be.
I did a bad job of selecting that; MrWord.com could tell me a better way to select.
Alright, Ctrl+K-- and the address, paste, is just that.
Simple.
Alright, but that's not what Selina wants to do.
Selina wants to link to a specific sheet and cell address.
Alright.
So, here, I've already built this.
What we're going to do is we're going to put the whole path and file name like before, then a pound sign or hash sign [ # ], and then the sheet name in apostrophes, even if it's only a single sheet [ name ]; close apostrophe; exclamation point; R99.
Now, take that, Ctrl+C. Now, you've got to be really careful.
I screwed this up at first.
Putting xlsx there, make sure the extensions are right.
Make sure all the path is right, and so on.
So, let's come back to Word, and we're going to go to a specific sheet and cell; Ctrl+K; paste; alright, so, the whole thing, C:\FolderName\FileName.xlsx#'SheetName'!R99.
Alright, but, hey, my recommendation-- don't worry about that.
Let's just do this.
Let's say that we want to come here and jump to this cell.
What I'm going to do is I'm going to select that cell.
I'm going to name it.
I'm going to give it a name: "Jumptome," like that.
Or, how about "Jumphere"?
"Jumphere”-- that's a great name.
Press enter, like that, and we'll go back up to A1.
Here, we'll be at A1, and we'll save this in the title card.
Save; File; Close; come back to Word.
And, for a jump to a Named Range, I think this one is even easier: Ctrl+K, alright, and in this one, we just put the whole workbook name path, workbook name path, pound sign, and the name of the named range (C:\FolderName\FileName.xlsx#NamedRange) like that.
Click OK.
Alright, so, we're in Word.
The first hyperlink is just going to open the file.
Ctrl+click.
They warn me; I'm sure I could get around this by saving it in a trusted folder.
And, they open Excel just at the exact same spot where I had it before.
File; close; and then try this one more linking to a specific sheet the question sheet R99.
Ctrl+click, and they go to the question sheet and scroll to R99.
Now, they didn't scroll so that way R99 was in the top left-hand corner cell, but at least it's in the visible window.
Alright-- File; close; and then the last one, to a Named Range-- easier to set up-- you don't have to worry about the apostrophes-- Ctrl+click; yes; and it jumps to the report sheet "Jumphere" for test 1.
For me, I'm going to say that's working.
I'm sure if you had hoped it had jumped to this spot, like that, you're going to say that this is not working.
But, that's the way life goes.
To Microsoft, this is working.
Well, hey, for tips like this tip-- 617 Excel Mysteries Solved-- check out this book: Power Excel with MrExcel.
The 2017 edition is the latest version, even though we're already in 2018.
The next time I'll update this will be in 2019, so you're safe to buy it now.
Alright, recap-- Selina wants to know if you can link from Word to a specific sheet and cell in Excel.
Yes.
If you build the link carefully enough.
The format is C:\FolderName\FileName.xlsx#'SheetName'!A1-- or, whatever cell you want to go to.
Or, easier, if you set up a Named Range, C:\FolderName\FileName.xlsx#NamedRange.
Well, hey, I want to thank Selina for sending the question in, and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Alright, so, hey, let's just see if we can get rid of that irritating message.
We go to File; Options; (I'm in Word for this-- probably could do it from either side); Trust Center; Trust Center Settings; Trusted Locations; I'm going to Add a New Location; type the location there; click OK; click OK; super annoying-- you have to close Word; reopen Word; and then the trusted locations are updated.
Now when we click, Ctrl+click, it will not hassle you and ask you to go to a potentially untrusted location.
