Excel Hyperlink Drop Down List - Navigate to Another Worksheet

Video Statistics and Information

Video
Captions Word Cloud
Reddit Comments
Captions
hi it's chester at blue peak and computer training in this video i'm going to demonstrate how to create a drop down menu of hyperlinks to other sheets in your workbook first step is you'll need a sheet with your sheet names on as i've got there and then you'll need a sheet where you're going to have your drop down list on in that sheet click in the cell where you want the drop down go to the data tab on your ribbon data validation allow list source is on your sheet names workbook select those cells click on ok now you've got your drop down list of sheet names now i'm going to do a little formula here you're not going to need it eventually but address is what we need this function will return a cell reference with a sheet definition row number column number now i'm not really interested in linking to a specific cell but if i wanted to link to cell a1 then it'll be one one row position the column position the next two arguments are non-mandatory as you can see apps number and a1 we don't need them in this context so just two commas that do then i'm on to the sheet text so the sheet text is going to be from here from b1 whatever is selected here if i close the brush and press enter you can see that it's returning the sheet reference alpha limited cell a1 if i change this to a different sheet it returns that cell reference this address is going to act as our hyperlink address to get it to work within a hyperlink what you're going to have to do is create a named reference i'll just show you how that works i am going to go up to formulas here i'm going to go to name manager new i'm going to call this sheet link no space is allowed and it's going to refer to any cell doesn't matter what it is at the moment we're going to have to replace this for the moment click on close and then what we can do is create a link that points to that named reference i'm going to do control k to create the hyperlink can you see under defined names in place in this document i've got sheet link there i'm going to click on ok at the moment that's not going to do anything useful because you're linking to a particular cell but what we want to do is replace that simple cell reference in our sheet link named reference to this formula this formula that returns this address the reason why i've created it prior to putting the formula in there is because once i put a formula in there it won't appear in your define names list i'll show you that that is true so what i'm going to do is i'm going to take this formula here copy it and i am going to go into the name manager and i'm going to edit it and i'm going to paste that formula in there click on ok click on close and now you'll see that it doesn't appear under defined name so you have to kind of point to it before you paste the formula in can get rid of that now we're not quite there you'll see that if i click on alpha limited whatever it doesn't quite work these are not quite hyperlinks that are working reference isn't valid so it's trying to point it somewhere and that is because our address formula is just returning a text string what we want to do is to return the actual cell reference and sheet reference that that text string is describing and the way we do that is we use the indirect function so take a text string and make it a a reference a proper reference that we can use if i click on ok now click on close let's see if this now works if i click on alpha limited click on that you can see it takes me to that particular sheet let's try another one xyz limited click on that it takes me to the correct sheet okay that's all i wanted to cover in this video hopefully you found it useful please subscribe if you have and i'll see you next video
Info
Channel: Chester Tugwell
Views: 6,153
Rating: 4.6363635 out of 5
Keywords: excel drop down list link to another worksheet, drop-down list of hyperlinks, drop down list to navigate worksheets in excel
Id: h5z2TA6KX7Y
Channel Id: undefined
Length: 4min 42sec (282 seconds)
Published: Mon Jan 18 2021
Related Videos
Note
Please note that this website is currently a work in progress! Lots of interesting data and statistics to come.