Drop Down List of Hyperlinks - Excel Hyperlinks Tip

[@teoxengineer]

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

[@Computergaga]

Thanks Emre.

[@kalidmomen2245]

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.

[@Computergaga]

You're welcome, Kalid. Happy to help.

[@patrickschardt7724]

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

[@Computergaga]

Awesome! Glad to be able to help Patrick.

[@vusalhuseynov3559]

Thorough explanation! Thank you very much.

[@Computergaga]

You're very welcome.

[@jimfitch]

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!

[@Computergaga]

My pleasure Jim. Thank you.

[@chawkibouderbala9418]

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 ..👍

[@Computergaga]

Great! You're welcome, Chawki. Thank you.

[@omars6665]

Great video as always. Can you have a drop down list where the options can link to opening and sending an email?

[@yisurya]

Is anyone going to answer this amazing question ????

[@manojsrikanth8663]

Really very nice video ❤

[@Azam_Khan0]

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.

[@deviguragai1496]

Thanks a lot sir. Your video helped me a lot at work.

[@Computergaga]

You are most welcome. Great to hear that.

[@knowledgeiseverything2404]

Very useful, Thank you very much ❤

[@Computergaga]

Thank you 😀

[@ezelkarlkl1284]

thank you very much for this video. How can we use for Column B not only B1

[@zahir585]

Lovely to watch your videos and continue learning. Keep it up ✌🏻

[@Computergaga]

Thank you Zac. Your comments are appreciated.

[@mohideenthassim7180]

Many thanks Alan great tip. Also have great festive season and a happy new year 2020. Cheers Mohideen

[@Computergaga]

Thank you Mohideen. All the best to you and those dear to you.

[@user-sp7rp5tv6y]

Thank you for this video! Can you show one doing this with Google sheets? I am having trouble getting the hyperlink to stick.

[@Computergaga]

You're welcome. I'm not a big Google Sheets user though, so cannot help there.

[@elh7388]

You helped me SO MUCH! THANK YOU! I subscribed :)

[@Computergaga]

Great to hear. You're welcome, Eline.

[@hassanalshekha1994]

Thank you sir...Much appreciated helpful sharing.

[@Computergaga]

You're welcome, Hassan. Thank you.

[@GeorgeAJululian]

Thank you This is really a great video!

[@Computergaga]

Thank you very much, George.

[@biancagarcia6597]

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?

[@wakjiraumetta252]

Thanks for this useful tutorial...

[@Computergaga]

You are most welcome

[@sasavienne]

Thank you Alan. That is useful as usual. Salim..

[@Computergaga]

Thank you Salim.

[@sasavienne]

Thank you so much for every single piece of Excel information I could get from you..

[@redhaakhund1271]

Great 👍👍👍👍👍🙏

[@Computergaga]

Thank you! Cheers!

[@johnhackwood1568]

Nice one Alan!

[@Computergaga]

Thank you, John.

[@Wildanperdanaputra]

Very helpful, thank you very much

[@Computergaga]

You're welcome. Thank you Wildan.

[@isaiahlongoria28]

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.

[@rohitharathnaweera8585]

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)" ?

[@Luciano_mp]

Amazing trick. Thank you.

[@Computergaga]

You're welcome Luciano. Thank you.

[@pratibhakudtarkar8912]

Fantastic

[@Computergaga]

Thank you so much 😀

[@captarius4179]

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.

[@ankursharma6157]

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!

[@Computergaga]

Great! Yes, that is absolutely fine. For anyone reading this, there should be one less ) on that alternative formula provided and it will work. Thanks for the tip 👍🏼

[@steverhodes7020]

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?

[@Computergaga]

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)

[@smileschildcarecentre25]

Informative Thanks

[@Computergaga]

Thank you 👍

[@badalrobinson1446]

Thank you so much

[@Computergaga]

You're welcome, Badal.

[@dggu5694]

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!

[@saran985]

Hi, it looks so cool. But this doesn't work when the sheet is hidden. How to use this same methodology using VBA?

[@geey7476]

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.

[@munchems]

What to do if sheet being linked to has a different name?

[@Computergaga]

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.

[@mahfuzhaque3439]

Great tip 😍

[@Computergaga]

Thank you, Mahfuz 👍

[@uniQue_XL]

Wow! Thanks!!

[@Computergaga]

You're very welcome. Thank you.

[@bisagesoftware9494]

Very useful

[@Computergaga]

Thanks a lot Ram.

[@mrriztv6184]

Perfect sir

[@Computergaga]

Thank you very much.

[@NewForestDragonflies]

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?

[@Computergaga]

Thank you. I'm not sure on linking to a website. I would need to look into it.

[@NewForestDragonflies]

@@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

[@gunfiresmoke]

how do i use the address and count function to jump down to a different row number

[@ariadnagarcia4599]

How would you use the MATCH function instead of 1,1?

[@fsdemesa]

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!

[@Computergaga]

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.

[@fsdemesa]

Thank you for that! I love your videos! ❤️

[@Computergaga]

Thank you 😊

[@sandrazak735]

how can I use this function to take me to a document in a folder outside of excel?

[@DrumLineXp]

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.

[@ExceluniForAll]

greaaaattttttt

[@Goldenmicheal16]

Thank you we can make a dependent hyperlinkdropdown to normal dropdowbn?

[@Computergaga]

You wouldn't worry about the hyperlink for this. It would just be a drop down of text options.

[@marttygee3722]

Hi, why is it when I do this, it says "Reference isn't valid." Thank you anyway.

[@gregorydennis1669]

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.

[@FGar90]

Can this also be used to link pdf files from a folder?

[@Computergaga]

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.

[@ezelkarlkl1284]

When delete cell value in B1 and try to add value again, function is not working.

[@chrisorosco8436]

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.

[@Computergaga]

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"))

[@fionamurphy1000]

@@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?

[@himanshulohani3279]

How to create search option in this drop down. If the list is large, then its difficult to search even in the drop down

[@Computergaga]

I have this video on a searchable list kzbin.info/www/bejne/e5LCcnamarxrsKs In Excel Online, the lists have the search option naturally.

[@allabout1135]

If to hide tabs. This trick still will work?

[@Computergaga]

The link won't access a hidden sheet. I have a video on to get a hyperlink to a hidden sheet to work - kzbin.info/www/bejne/fYiZYqOvr8mfnpI

[@madhavikulkarni5802]

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

[@Computergaga]

I don't know. Do you get a message?

[@saracoppard2997]

Is it possible to link to a hidden sheet?

[@Computergaga]

No, but I have a video with a VBA solution kzbin.info/www/bejne/fYiZYqOvr8mfnpI

[@saracoppard2997]

@@Computergaga thank you very much. I'll take a look now.

[@bettyhoutman2183]

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?

[@Computergaga]

I couldn't say without seeing it Betty. Sorry.

[@meganathanve444]

Hi sir,,I am using Excel 2016..,,Defined Names option available but newly added Name not updated in Hyperlink Dialog box..,,Please guide me..,,

[@Computergaga]

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.

[@judynguyen4779]

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?

[@ezelkarlkl1284]

Dod you find any solutions ?

[@judynguyen4779]

@@ezelkarlkl1284 Nope.

[@KyleH5453]

How is this done on google sheets?

[@Computergaga]

Sorry, do not currently know on Google Sheets.

[@ronakgujarathi]

It's not working on office 365, Can you help me with it

[@Computergaga]

Office 365 will not be an issues as I also have that. It is probably a typo on a sheet name.

[@nangnainimanchey5868]

How can I apply this in google sheet

[@Computergaga]

I am not sure, I do not use Google Sheets

[@MohdSaifoulAzuan]

Tq sir

[@Computergaga]

My pleasure Mohd.

[@mohamedchakroun4973]

Thankssssssssssssssssssssssssssssssss

[@Computergaga]

My pleasure Mohamed.

[@gojkoprzulj990]

i recreated your video but it does not work here

[@Computergaga]

Sorry to hear that. There will be a reason. Check it through.

[@gojkoprzulj990]

@Computergaga when I copy the formula in the "my_sheet" it hilights the part from: 1,1,,,indirect