Excel Magic Trick #139: HYPERLINK function & Drop Down List

[Sherlock69Holmes]

Its Awesome , In My scenario had to refer the sheets of workbook

But say , I had 100 sheets. i created list and a dropdown to select the link , & then hyerlink function as shown , its not working..

[Sherlock69Holmes]

Plz Plz guide me Mike

[ExcelIsFun]

@Sherlock69Holmes , I am sorry I do not know what is causing the problem. Try posting to:

mrexcel [dot] com/forum

[meranewid]

sir my email address is mrfuad2007@gmail.com ..

[ExcelIsFun]

@meranewid , if you are an educational site, yes! Don't embed if you are using videos for profit.

[meranewid]

Question 1: Sir Please explain to me how can make multiple entries"non linkable URLs" in a colum a link able (mouse over , hand comes as Hyperlink is there) ? for one entry its very simple that we use to edit the cell and press enter "it becomes hyper link"

Question 2: its basically a viseversa of the above question that how we remove multiple hyper links if there are more than 4000 clickable URLs , for one URL its only to right click > remove hyper link but for 4000??

Plz plz plz help..

[meranewid]

very useful videos.. sir can i add (embed) your all videos from youtube channel to my website?

[chopinsiblos]

i mean drop down list with hyperlink in it and link it to, let say for example i have a dropdownlist with name 1 to name 5, name 1 wll link to sheet 2, name 2 linked to sheet 3 and so on...thank you for the rply...your tuts help me a lot...GodBless,more power!

[ExcelIsFun]

@chopinsiblos , like I said: when you Ctrl + K, then click on "Place In This Document" - that is how you link to a sheet.

[chopinsiblos]

GUDDAY SIR...YOUR TUT IS very helpful...i just want to ask if it is posible to have a hyperlink in dropdownlist and link it to individual different links within the workbook...thanks in advance

[ExcelIsFun]

@chopinsiblos , yes, when you Ctrl + K, then click on "Place In This Document"

[Karin16121968]

I tried with this formula

=HYPERLINK("[Excel tricks.xls]test!"&A1;A1)

[Karin16121968]

Many thanks for you reply, but what do you mean to puting in file extension?

[Karin16121968]

Hi, many thanks for your tutorial. I have one problem it does not open the specified file. I would appreciate your help.

=HYPERLINK("[Excel tricks]Test!"&A1;A1)

Sorry my English is not that good

[ExcelIsFun]

@Karin16121968 , try puting in file extension.

[vasureddy86]

hi i want to create country & their currency list for i have created dropdown list but i dont know how link those pls help me exp: if i select usa in one cell in another cell it has show their currency .

thanks & regards

srinivas

[ExcelIsFun]

@vasureddy86 , I do not knoe how to do that. Try:

mrexcel [dot]com/forum

[nixs20]

hey man! i have a problem when i click the enter in the hyperlink function when searching a name i doesn't go to the specific name... it just highlight all the names..any suggestion?

[MissMerian]

I get an error that says Reference is not valid when I did this hyperlink formula to a data validation list. Is this because I need an add-in to allow it to function? I made sure the workbook name was correct in formula. I tried yours as well by downloading from site. It said Cannot find specific file. ???

[ExcelIsFun]

Try this video to help find the files:

excelisfun Search for Excel Videos, Playlists & Download Workbooks

I do not know what is causing the #REF! error. The #REF! error means that it does not see the reference as a ligitimate reference.

[earthworm82]

i tried it , its still not working , its showing the same message

" reference not valid"

[ExcelIsFun]

Are you asking about a link to a web site or to another location in the workbook?

Web: If the message says: "The address of this site is not valid. Check the address and try again", it is probably an incorrect web address.

Another location: make sure you have the correct syntax and name (example correct square brackets and explanation points, etc.)

Either way, it is more than likely an address problem.

[1natsaroo]

@ExcelIsFun I had this same problem The "reference is not valid" probably because you named the "range of cells" or the "List" but did not individually name EACH cell. Therefore when you ask it to look for "Name1" for instance it can't find it because you have not named the cell that has the name "Name1" in it "Name1" Confused? There is a difference between naming the cell and what is in the actual cell as text. Hope this helps.

[earthworm82]

Thanks waiting for the Video . Please do let me know :)

[ExcelIsFun]

Dear earthworm82,

I just posted a video on linking to a different sheet in the same workbook:

Excel Magic Trick 326: Hyperlink Worksheet To Worksheet

As for the first problem, try this formula (single quotes, not double - quote before !, not after):

=HYPERLINK('[Book1]Sheet2'!&D8 ,D8)

--excelisfun

[earthworm82]

i didnt worked which u told me

can you please make a sample and upload it here

waiting

[ExcelIsFun]

Dear earthworm82 ,

A video will be posted tomorrow on this topic.

--excelisfun

[earthworm82]

I cannot understand regarding hyperlinking cell in another sheet . as when i tried , it says cannot open specific file " i tried to select sheet 2 ,

this is the formula which i used

=HYPERLINK("[Book1]Sheet2!"&D8­,D8)

Secondly i would like to know u told about hyperlinking list to another cell in other sheet in same workbook . Can you please guide how to hyperlink the list in drop list to directly to another sheet ,even linked with different sheet ?

[ExcelIsFun]

Dear earthworm82 ,

Point #1: try saving, closing and then opening the workbook before making a link to it.

Point #2: To link to a sheet in the workbook, in the Insert Hyperlink dialog box, click on "Place in This Documnet" on the left side.

--excelisfun

[BornIntoMusic]

Update - My problem was that I renamed the link to better describe to the user what document they were going to open. (This is very important.) So is there any way to rename the link and have the drop down list properly follow it?

[ExcelIsFun]

Dear BornIntoMusic,

You can: Ctrl + K to add hyperlink without the HYPERLINK function, then choose file from "Look in" drop down and type name you want from "Text To Display".

--excelisfun

[SchaumHaque]

Hello, is there a way to update the links when the source file is closed? Any suggestions for Excel Dashboards? Many thanks

[ExcelIsFun]

I don't know an answer to the 1st one. And I do not have any Dashboard videos.

[BornIntoMusic]

Thank you so much for your help. I'm trying to do something similar to the first part of this video. What's different is; instead of going to a website, I want the hyperlink to follow to a document on a shared network drive. When I followed your instructions it returned with an error stating "Cannot Open Specified File". Any solutions or insight to this problem? Thanks.... Brandon