How to Enter Name of File and Automatically Turn It Into Hyperlink to Said File

Hi Folks,

So here's what I'm looking to do: I want to enter the name of a document into cell A7 for example and have it automatically link to the document named; this way when you click on the text entered, it opens the document like a hyperlink. So essentially I would have document name " Example ", I would type " Example " into cell A7, the text would still say " Example " but it would automatically become a hyperlink to the document. Does anyone know if this is a possibility, and if so, what formula would be necessary? Also, if nothing is entered in A7, I would like for it to remain blank. Any help is great. I'm playing with some formulas myself, and will post any findings. Thanks!

That would be manually entering the hyperlink. Here's my setup: I have a folder containing Associate Dossiers for each associate (Excel files named after the associate i.e. "Doe, John"); I also have Rosters for different locations that reference these files; what I would like to do (for people unfamiliar with creating hyperlinks) is input a formula in say A7 so that all an individual would have to do is type "Doe, John" and it would automatically become a hyperlink to the file while displaying the same text in the same cell. This is for people who have absolutely no experience with Excel...

=IF(A7="","",HYPERLINK("File Pathway"&A7&".xlsx", ""&A7&""))

If I enter the above formula into any cell but A7, it will automatically create the hyperlink while displaying the text in A7. If I enter it into A7, it displays a circular reference warning then puts a hyperlink 0; when the the text is clicked on, it says it cannot open file. Is there some way we can tweek that basic formula to do what I'm looking to do?

I apologize if I'm not clear enough with my questions; I'm not talking about editing the formula. If I type "Doe, John" into A7, A7 then automatically becomes a hyperlink to file "Doe, John" and displays as "Doe, John" as originally typed. That would be what the formula would do. I understand how to create the hyperlink in A8 based upon what's entered in A7, but that's not what I'm looking for. I want all of this to happen automatically within the same cell, and all the user would have to do is type the associate name in the cell. I don't know if that's possible in Excel as is or with Add-Ins, which is why I'm checking on here. Is that a bit clearer? I know I've come across some pretty amazing add-ins, so maybe someone has a solution to bypassing the circular reference error...

Sorry, haven't ventured much into macros yet...I know what they are, but I haven't the faintest idea of how/where to input that code and utilize it. Would be grateful for instructions. Thanks for all your help so far, by the way. I appreciate the time and effort.

Never mind, I found out where and how to properly enter it. The code creates a hyperlink with the entered text, so thank you very much for that. The only problem now is that the hyperlink won't open the file, but I can probably find a problem in the file pathway/wording somewhere. Thank you for your help, and I will post my findings when I can.

You are a God among men...Thank you so much for your epic genius. The only thing I'm running into now is that I have to clear the contents of a cell before entering in a different name, otherwise it opens the file of the previous name and throws up a run-time error, but that's not a big deal considering what you've just accomplished for me and hopefully countless others. Thank you again!

I made the change to range already, but the cells still need to be cleared by selecting "Clear Contents" otherwise it links the new text to the previous file. Not sure why, but I'm not concerned about that. Like I said, your help and success with this is amazing enough, so thank you. For some reason if I put a name in say A7 and then a name in A8, I can open each of those hyperlinks fine the first time, but if I go back into them, it brings up the Run-time Error '1004': Method 'Intersect' of object '_Global' failed. Currently Googling what that's about. If nothing else, I can protect the macros with a password, removing the debug option, so the user is forced to hit "End" and everything is fine.

For anyone else tackling this problem or looking for the same info, here's what happens: Enter Associate into A7, click on link, it opens associated file. Enter different Associate into A8, click on link, it opens associated file. If you then click on the link in A7 again, it opens the file but throws up the error "Run-time Error '1004': Method 'Intersect' of object '_Global' failed." It allows you to choose "End", "Debug", and "Help". Choosing "End" closes the error, and all is good. If you choose "Debug", it shows that the error results from the "Intersect(Target, ActiveSheet.Range("A:A")) Is Nothing Then" portion of the macro. If you choose end, and continue opening that same file, it no longer shows the error. However, if you click on the link in A8, it will pull up the file with the error and do the same thing.

Still looking for a solution on my end. Anyone else figured anything out?

look at these threads they should point you in the right direction

http://www.excelforum.com/l/965684-asdf.html


http://www.excelforum.com/excel-prog...extension.html

Thanks for the response, Toonies. These examples aren't a solution to what I'm looking for. I need to be able to input a file name in A5 and have the hyperlink be placed back into A5; essentially it automatically becomes a hyperlink to the file. The macro code listed earlier in this thread does exactly that, however it has a couple hiccups. That's what I'm looking to solve now: the hiccups.

ok try this

Please Login or Register  to view this content.

And with a few minor alterations to fit actual file paths, we have a winner! You are wonderful! Thank you so much for your help Toonies!

One thing I just noticed for anyone else who might be interested in using this code, in order to change the name in a cell and change the hyperlink location, you have to right-click and clear contents before entering the new name, otherwise it will still jump to the previous file. Not a big deal, but want others to be aware.

Glad I could help, please mark your thread as Solved