In this video with a ModelOff finalist, Dublin's Paul Kehoe shows us an annoying problem with hyperlinks that break when you change the sheet name and an easy workaround. The video then shows how to select a hyperlink cell without following the hyperlink, and how you can easily create a "Back to Menu" across many worksheets.
Transcript of the video:
Oh! Hey welcome back to the model off 2013.
I'm here with Paul Kehoe.
Paul's from here from Dublin, Ireland.
You're one of the 16 finalists this year. Welcome.
Paul: Thank you Bill.
Bill: All right! so, hey Paul has a great trick for us today. What do you have today?
Paul: And Bill it's just a suggestion around, how better use of hyperlinks and hyperlinks can be very useful when, linking from one sheet to another particularly when you want to set up an index page but there's one area where Excel and it doesn't respond well.
Bill: Okay so, you want to have like a table of contents and be able to link out to various parts of the model or something like that. right?
Paul: Exactly yes.
Bill: Okay so, I think I've done hyperlinks, go ahead and show us, show us how to set one up and what the problem is.
Paul: All right, Bill so, if I wanted to create a hyperlink I just click on the Insert, Hyperlink and normally go place in document and if I want to pick link to the order another sheet, so the datasheet this case just click on the sheet name just click on A1 and press OK and which case I click here it will jump over to the sheet.
Bill: Nice oh, but wait I misspelled the datasheet you gotta fix that.
Paul: So, if we re-correct a sheet name, I now here's the hyperlink to find Excel is unbale to read it.
Paul: So, Excel can deal with the sheet being renamed if it's in a formula, but if it's in a hyperlink, then it doesn't work exactly what a horrible problem. Okay so, I guess one thing you can do is make sure that all your sheets are spelled right before you create a hyperlinks.
Paul: True.
Bill: Okay, but you have a better way but yes all right let's hope my Paul: I suggest it would be to create a range name.
So, go to somewhere on your target sheet and clicking in a range name.
So, typically I just go sheet data that is now, created a range name so I hit the F3 key I can see that a range name good okay we created and if I go back to the menu sheet.
I'm going to insert hyperlink again and this time I'm gonna click on define names datasheet, press OK and you see it jumps to it and in this case if I rename the name of the sheet and then I try to hyperlink it again it jumps to...
It will now, respond to it.
Bill: That is a great trick. So, yeah you have a large table of contents with a lot of different sheets that's a great way to go.
Now, hey, one thing I saw you do there that you should go back to explore, go back to the menu sheet and go up one cell for me.
All right! So now, you wanted to go select cell B3 but you didn't reach for the mouse to do that you actually clicked in cell B2 and then you went down.
Why didn't you just click on B3 to select B3?
Paul: If I had click, the hyperlink is already been set up.
Bill: Oh! Yeah.
Paul: So, I would have received the error message.
Bill: Okay so, you click near it and then use the arrow key to move down to it.
Paul: Yes.
Bill: All right, that's good that's the same way that's the same method I use.
I'm gonna, I'm gonna give you a trick.
So, here you flew all the way to New York and now you're gonna get this trick someone saw me do that in the podcast and they wrote in they said hey, all you have to do go and click away somewhere else and go back to the menu sheet and now, you're gonna click and hold on B3 click and hold on B3 for just there we go, as soon as it changes from the hand to the plus sign.
You can now, let go and you're good to go.
So, you don't have to scoot in from the side although I still scoot in the side.
Paul: Brilliant, and then just an extra layer to this idea Bill is. So, at the moment we've got our index sheet jumping to every page then what would also help then is on every page we have to jump back to the index.
Bill Right! Okay.
Paul: So, back on the menu sheet, if I just click up sheet menu enter, and then typically with always the top of each sheet create a hyperlink menu and this is, this reference would actually put on every sheet and this would then mean I can always jump to the index sheet and from there jump back to your next sheet that I want to go to.
Bill: Good now, so a question that I have. So, let's say you have 40 different sheets and we wanted to add the jump back to the menu on all 40.
Do you have to do insert hyperlink on each one or once I have that first one done can we copy and paste, paste, paste, paste, paste.
Paul: We can certainly copy individually across them.
Bill: Okay.
Paul Hope you have never tried.
Bill: All right and good will have to that'll have to be lost right in another actually let you know what let's try it I mean just you can edit this part out if it doesn't work yet.
So, we hit the new worksheet icon a whole bunch of times.
Good, Okay so, now, go back to your data sheet, So, control+C to copy yeah control+C and then we're gonna be really brave and try group mode there's no way this will work.
All right! So, worked on sheet 3. Isn't that amazing let's see if the hyperlink works, that is beautiful.
Paul: This is good.
Bill: Paul, the guys from Microsoft who actually designed Excel are like 50 feet away from us or for you 20 meters right right yeah and I was gonna run back into the room and say hey you screwed this up that you didn't give us a way to copy the hyperlink in group mode but to my surprise it actually works.
So, we'll give, will give them credit for that one.
Paul: Yes.
Bill: All right! well hey, Paul best of luck in my office thanks for being here today.
Paul: Bill, thank you very very much.
I'm here with Paul Kehoe.
Paul's from here from Dublin, Ireland.
You're one of the 16 finalists this year. Welcome.
Paul: Thank you Bill.
Bill: All right! so, hey Paul has a great trick for us today. What do you have today?
Paul: And Bill it's just a suggestion around, how better use of hyperlinks and hyperlinks can be very useful when, linking from one sheet to another particularly when you want to set up an index page but there's one area where Excel and it doesn't respond well.
Bill: Okay so, you want to have like a table of contents and be able to link out to various parts of the model or something like that. right?
Paul: Exactly yes.
Bill: Okay so, I think I've done hyperlinks, go ahead and show us, show us how to set one up and what the problem is.
Paul: All right, Bill so, if I wanted to create a hyperlink I just click on the Insert, Hyperlink and normally go place in document and if I want to pick link to the order another sheet, so the datasheet this case just click on the sheet name just click on A1 and press OK and which case I click here it will jump over to the sheet.
Bill: Nice oh, but wait I misspelled the datasheet you gotta fix that.
Paul: So, if we re-correct a sheet name, I now here's the hyperlink to find Excel is unbale to read it.
Paul: So, Excel can deal with the sheet being renamed if it's in a formula, but if it's in a hyperlink, then it doesn't work exactly what a horrible problem. Okay so, I guess one thing you can do is make sure that all your sheets are spelled right before you create a hyperlinks.
Paul: True.
Bill: Okay, but you have a better way but yes all right let's hope my Paul: I suggest it would be to create a range name.
So, go to somewhere on your target sheet and clicking in a range name.
So, typically I just go sheet data that is now, created a range name so I hit the F3 key I can see that a range name good okay we created and if I go back to the menu sheet.
I'm going to insert hyperlink again and this time I'm gonna click on define names datasheet, press OK and you see it jumps to it and in this case if I rename the name of the sheet and then I try to hyperlink it again it jumps to...
It will now, respond to it.
Bill: That is a great trick. So, yeah you have a large table of contents with a lot of different sheets that's a great way to go.
Now, hey, one thing I saw you do there that you should go back to explore, go back to the menu sheet and go up one cell for me.
All right! So now, you wanted to go select cell B3 but you didn't reach for the mouse to do that you actually clicked in cell B2 and then you went down.
Why didn't you just click on B3 to select B3?
Paul: If I had click, the hyperlink is already been set up.
Bill: Oh! Yeah.
Paul: So, I would have received the error message.
Bill: Okay so, you click near it and then use the arrow key to move down to it.
Paul: Yes.
Bill: All right, that's good that's the same way that's the same method I use.
I'm gonna, I'm gonna give you a trick.
So, here you flew all the way to New York and now you're gonna get this trick someone saw me do that in the podcast and they wrote in they said hey, all you have to do go and click away somewhere else and go back to the menu sheet and now, you're gonna click and hold on B3 click and hold on B3 for just there we go, as soon as it changes from the hand to the plus sign.
You can now, let go and you're good to go.
So, you don't have to scoot in from the side although I still scoot in the side.
Paul: Brilliant, and then just an extra layer to this idea Bill is. So, at the moment we've got our index sheet jumping to every page then what would also help then is on every page we have to jump back to the index.
Bill Right! Okay.
Paul: So, back on the menu sheet, if I just click up sheet menu enter, and then typically with always the top of each sheet create a hyperlink menu and this is, this reference would actually put on every sheet and this would then mean I can always jump to the index sheet and from there jump back to your next sheet that I want to go to.
Bill: Good now, so a question that I have. So, let's say you have 40 different sheets and we wanted to add the jump back to the menu on all 40.
Do you have to do insert hyperlink on each one or once I have that first one done can we copy and paste, paste, paste, paste, paste.
Paul: We can certainly copy individually across them.
Bill: Okay.
Paul Hope you have never tried.
Bill: All right and good will have to that'll have to be lost right in another actually let you know what let's try it I mean just you can edit this part out if it doesn't work yet.
So, we hit the new worksheet icon a whole bunch of times.
Good, Okay so, now, go back to your data sheet, So, control+C to copy yeah control+C and then we're gonna be really brave and try group mode there's no way this will work.
All right! So, worked on sheet 3. Isn't that amazing let's see if the hyperlink works, that is beautiful.
Paul: This is good.
Bill: Paul, the guys from Microsoft who actually designed Excel are like 50 feet away from us or for you 20 meters right right yeah and I was gonna run back into the room and say hey you screwed this up that you didn't give us a way to copy the hyperlink in group mode but to my surprise it actually works.
So, we'll give, will give them credit for that one.
Paul: Yes.
Bill: All right! well hey, Paul best of luck in my office thanks for being here today.
Paul: Bill, thank you very very much.
