Drop Down List of Hyperlinks - Excel Hyperlinks Tip
HTML-код
- Опубликовано: 5 июл 2024
- In this video, we create a drop-down list of hyperlinks to other sheets in the workbook.
I have created videos in the past on the HYPERLINK function and creating dynamic links from text on the worksheet.
In this video, though it is selected from a dropdown. A great idea for a report, table of contents or dashboard page. Where space is limited but you want simple and dynamic ways to navigate.
For this to work, we got smart we some Excel formulas using the INDIRECT and ADDRESS functions.
5 great INDIRECT function examples - • 5 Excel INDIRECT Funct...
Here are the video timings.
00:00 - Introduction
00:59 - Create the drop-down list
01:38 - Explanation of our approach
02:53 - Sample link in the drop-down
03:37 - INDIRECT function to hyperlink to another sheet
06:58 - Demonstrate the completed drop-down hyperlink list
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/2JvnnRv
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2t3netw
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2viGg3J
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1 
Хобби
So interesting and magic. Indirect is so wonderful function and if it is improved with new features, it will be more accurately usable and functional.
Thank you Alan for explaining us these hidden indirect and link properties together
Thanks Emre.
Thorough explanation! Thank you very much.
You're very welcome.
Very informative. I’ll be implementing this in my testing reports. I test the same unit in multiple ways and each sheet is its own data set taken at different dates and times. Having a cover page with this will be very helpful
Awesome! Glad to be able to help Patrick.
Very useful, Thank you very much ❤
Thank you 😀
Amazing trick. Thank you.
You're welcome Luciano. Thank you.
Lovely to watch your videos and continue learning. Keep it up ✌🏻
Thank you Zac. Your comments are appreciated.
Very helpful, thank you very much
You're welcome. Thank you Wildan.
I really wish I saw this vid sooner. I've searched everywhere to find this and here it is. All thanks to you ,you're such a great life-saver . ❤Love all the way from Somalia.
You're welcome, Kalid. Happy to help.
You helped me SO MUCH! THANK YOU! I subscribed :)
Great to hear. You're welcome, Eline.
Thank you sir...Much appreciated helpful sharing.
You're welcome, Hassan. Thank you.
Thank you This is really a great video!
Thank you very much, George.
Many thanks Alan great tip. Also have great festive season and a happy new year 2020. Cheers Mohideen
Thank you Mohideen. All the best to you and those dear to you.
Wow!
Thanks!!
You're very welcome. Thank you.
Awesome tutorial 😊👌
Could you please let us know how do we implement the same function in multiple cells.
I tried it in multiple cells, however, I am not able to figure out a way to implement it in multiple cells.
Hi Alan, appreciate your videos. Is there a way to change the hyperlinks in the entire column at once ? Or to make the hyperlink pick the right column when I add a column to the hyperlink referring sheet? My issue is when I add a column to my main sheet the hyperlink in another sheet in the same file picking the wrong column.
Thanks for this useful tutorial...
You are most welcome
Thank you so much Alan. This was very helpful. I had a question. Would be grateful if you could help. How do i go about hyperlinking different cells in the same tab. I don't need the hyperlinks to different sheets but to different cells in the same tab.
Great tip 😍
Thank you, Mahfuz 👍
Awesome !!! I made many lists by category and used it as a "web style" drop down menu lists , so usefull when whe have a lot of tabs ..👍
Great! You're welcome, Chawki. Thank you.
thank you very much for this video. How can we use for Column B not only B1
Thank you Alan. That is useful as usual. Salim..
Thank you Salim.
Thank you so much for every single piece of Excel information I could get from you..
Informative Thanks
Thank you 👍
Thanks a lot sir. Your video helped me a lot at work.
You are most welcome. Great to hear that.
Very informative! Is it possible for the reference cell be dynamic? In your example, it would only work if data validation is on cell B2, but cant figure out if the data validation are in the entire column (B:B). Stay safe!
Brilliant. Is there a way to add an additional hyperlink that allows me to go "back" to the previous workbook after selecting the hyperlink in the drop down list? I want to be able to click a hyperlink in the drop down list to go to that selected workbook, then have the option to click "back" or something to return to the workbook that I was just in.
Thank you so much
You're welcome, Badal.
Nice one Alan!
Thank you, John.
Great 👍👍👍👍👍🙏
Thank you! Cheers!
Hi!
Great video!
Please advise how to clear web addresses appearing in the drop down list of "Insert hyperlink > Existing file or web page > Current folder > address: (drop down)" ?
Very useful
Thanks a lot Ram.
Perfect sir
Thank you very much.
Fantastic
Thank you so much 😀
Would you be able to do another tutorial hyperlinking to the same tab rather than other tabs? And using the match formula instead of 1 for row number?
Hi Alan. Loving your work. Would it be possible for you to tell me how I can change this to link to defined names ranges rather than sheets?
Thank you, Steve. For defined names, if the names matches the value in cell B1, (the cell containing the list) then you can use the formula INDIRECT(B1)
Great video as always. Can you have a drop down list where the options can link to opening and sending an email?
Is anyone going to answer this amazing question ????
Very clever, Alan! I’m going to have to watch this again ... maybe more than once. 🥴 I’ve seldom used ADDRESS & INDIRECT, but it’s clearly time to go to school on these functions. I think I’ll be able to make good use of drop-down hyperlinks. Thanks for teaching it!
My pleasure Jim. Thank you.
Really clever stuff thanks! is there a way I could use this to hyperlink to a website from a drop down list? if so what formula would I need?
Thank you. I'm not sure on linking to a website. I would need to look into it.
@@Computergaga ok thanks! I did it with VBA in the end, but would be nice to find a formula fix if you think of anything
This is really a great video! Thank you!
Is there a way for us to make that dropdown list link to a table within the same sheet?
Thanks for your help!
You're very welcome 👍
Yes, this is actually a little easier. If the drop down list was in cell D2 and had a list of table names (this sheet or others, does not matter) then the formula would simply be =INDIRECT($D$2) Still put it in a named range like in the video.
Thank you for that! I love your videos! ❤️
Thank you 😊
Hi Alan,
Thank You for Your Awesome Video.
I will be Grateful if You respond to the following question:
Q.
• Formula used in the video: =INDIRECT(ADDRESS(1, 1, , , INDIRECT("B1")))
• Could We use: =INDIRECT(ADDRESS(1, 1, , , $B$1)))
I tried INDIRECT(ADDRESS(1, 1, , , $B$1))) - it is returning the same effect.
Please advise.
Thank You!
Thank you we can make a dependent hyperlinkdropdown to normal dropdowbn?
You wouldn't worry about the hyperlink for this. It would just be a drop down of text options.
How would you use the MATCH function instead of 1,1?
Hi, it looks so cool. But this doesn't work when the sheet is hidden. How to use this same methodology using VBA?
How can I use this logic to use a hyperlink in the document itself. For example I created hyperlink to reference cell A1:D5 and now from the dropdown I want to be able to select the data validation and link to the existing A1:D5 cell.
Tq sir
My pleasure Mohd.
Can this also be used to link pdf files from a folder?
Sure. You would need the path to the PDF in a cell somewhere or have a way of constructing it. If you can hyperlink to it, then this list can link to it.
how do i use the address and count function to jump down to a different row number
how can I use this function to take me to a document in a folder outside of excel?
when I add the Indirect function formula in the Defined names by clicking on Name manager and selecting the name space I had created, it is not saving. Hence unable to link it dynamically. What could be the issue. Thanks
I don't know. Do you get a message?
How could you use the dropdown list to hyperlink to a CELL POSITION on the same sheet? (as opposed to jumping to the fixed A1 cell on the sheet selected in the list). For example, my drop-down list range is a set of text values (e.g. company names). I want to hyperlink to a cell position on the same sheet, located at Column B and the Row determined by a Match function matching the name in the drop-down list.
Hi Chris,
This formula works. Make note of the sheet written at the end and the +4 because my range starts at B5 and I need the sheet row number.
=INDIRECT(ADDRESS(MATCH($B$2,$B$5:$B$13,0)+4,2,,,"Sheet1"))
@@Computergaga Hi, I'm trying to do something similar to Chris in that I want to jump to a cell within the same worksheet. I've almost got it working but it jumps down rows instead across columns. I have a process that covers 19 columns and would like to be able to jump to a particular step using the dropdown list of the steps with hyperlinks.
The formula I currently have in there is =INDIRECT(ADDRESS(MATCH(Status!$B$1,Status!$G$2:$CZ$2,0)+6,2,,,"Status"))
Any suggestions?
Hi sir,,I am using Excel 2016..,,Defined Names option available but newly added Name not updated in Hyperlink Dialog box..,,Please guide me..,,
Hi Meganathan, it won't appear if a formula is driving it. We need to create a normal named range, set the hyperlink then do the formula last.
how do you droplist with a hyperlink to a specific column in the same sheet but maybe like to A8, and the next one link to W24?
Dod you find any solutions ?
@@ezelkarlkl1284 Nope.
If to hide tabs. This trick still will work?
The link won't access a hidden sheet. I have a video on to get a hyperlink to a hidden sheet to work - ruclips.net/video/GW61pyzdlh0/видео.html
Tried this but it didn't work...finally go it to work when I duplicated the conditions from the video.. Then tried in my workbook - no dice. I finally have it working. The solution: my worksheet names are dates ("25-Sep", for example). I used the TEXT formula on my list page to convert the dates to a text format, so now the indirect and address formulas work.
I am trying to do a menu page and it will do the first drop down list correctly, when I go to another drop down list for another spot it tells me my reference is not valid. What am I doing wrong?
I couldn't say without seeing it Betty. Sorry.
What to do if sheet being linked to has a different name?
You would need a way of matching the names first. Maybe a lookup table to substitute the name in the list for the name that matches the sheet for INDIRECT to use.
How to create search option in this drop down. If the list is large, then its difficult to search even in the drop down
I have this video on a searchable list ruclips.net/video/Ea_ACp5W8zI/видео.html
In Excel Online, the lists have the search option naturally.
When delete cell value in B1 and try to add value again, function is not working.
How can I apply this in google sheet
I am not sure, I do not use Google Sheets
Thankssssssssssssssssssssssssssssssss
My pleasure Mohamed.
How is this done on google sheets?
Sorry, do not currently know on Google Sheets.
It's not working on office 365, Can you help me with it
Office 365 will not be an issues as I also have that. It is probably a typo on a sheet name.
Is it possible to link to a hidden sheet?
No, but I have a video with a VBA solution ruclips.net/video/GW61pyzdlh0/видео.html
@@Computergaga thank you very much. I'll take a look now.
Hi, why is it when I do this, it says "Reference isn't valid."
Thank you anyway.
i recreated your video but it does not work here
Sorry to hear that. There will be a reason. Check it through.
@Computergaga when I copy the formula in the "my_sheet" it hilights the part from: 1,1,,,indirect
Thank you for this video! Can you show one doing this with Google sheets? I am having trouble getting the hyperlink to stick.
You're welcome. I'm not a big Google Sheets user though, so cannot help there.