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
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.
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
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!
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)" ?
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.
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.
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.
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.
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?
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.
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.
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)
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
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!
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?
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.
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.
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!
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 👍🏼
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.
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.
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.
Thorough explanation! Thank you very much.
You're very welcome.
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.
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 😀
Thanks a lot sir. Your video helped me a lot at work.
You are most welcome. Great to hear that.
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.
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)" ?
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.
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 This is really a great video!
Thank you very much, George.
Thanks for this useful tutorial...
You are most welcome
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..
Nice one Alan!
Thank you, John.
Really very nice video ❤
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.
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?
Lovely to watch your videos and continue learning. Keep it up ✌🏻
Thank you Zac. Your comments are appreciated.
how do i use the address and count function to jump down to a different row number
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.
Very helpful, thank you very much
You're welcome. Thank you Wildan.
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 ????
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.
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 can I use this function to take me to a document in a folder outside of excel?
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.
Great 👍👍👍👍👍🙏
Thank you! Cheers!
Hi, it looks so cool. But this doesn't work when the sheet is hidden. How to use this same methodology using VBA?
Informative Thanks
Thank you 👍
Amazing trick. Thank you.
You're welcome Luciano. Thank you.
How would you use the MATCH function instead of 1,1?
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?
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.
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.
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.
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 tip 😍
Thank you, Mahfuz 👍
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.
Fantastic
Thank you so much 😀
Thank you so much
You're welcome, Badal.
Very useful
Thanks a lot Ram.
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
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 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 th-cam.com/video/Ea_ACp5W8zI/w-d-xo.html
In Excel Online, the lists have the search option naturally.
Wow!
Thanks!!
You're very welcome. Thank you.
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!
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.
Perfect sir
Thank you very much.
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.
When delete cell value in B1 and try to add value again, function is not working.
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.
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!
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 👍🏼
Hi, why is it when I do this, it says "Reference isn't valid."
Thank you anyway.
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 - th-cam.com/video/GW61pyzdlh0/w-d-xo.html
How is this done on google sheets?
Sorry, do not currently know on Google Sheets.
greaaaattttttt
How can I apply this in google sheet
I am not sure, I do not use Google Sheets
Is it possible to link to a hidden sheet?
No, but I have a video with a VBA solution th-cam.com/video/GW61pyzdlh0/w-d-xo.html
@@Computergaga thank you very much. I'll take a look now.
Well well
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.
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.
Tq sir
My pleasure Mohd.
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
Thankssssssssssssssssssssssssssssssss
My pleasure Mohamed.