excel hyperlink question

[BuckeyeNation27]

I have a spreadsheet with 6500 entries. One column for those entries is file name, and refers to a PDF. All PDFs are in the same folder, so if the cell says bn27 then there's a file in this folder called bn27.pdf.

Is there a way too make the file names hyperlinks to their corresponding PDFs? Here's how I got it to "work", but I'd rather just have the original column turn in to hyper links:

I entered the beginning of the filename path in 1 field (D1). Let's pretend the path is C:\AwesomeStuff\
In a new column, I entered =HYPERLINK(CONCATENATE($D$1,A2,".pdf"),H2)

The CONCATENATE function obviously turns that formula into =HYPERLINK(C:\AwesomeStuff\________.pdf,__________) where ________ is the file name.

Doing it this way means I have to have data in D1 and that new column, which is just an exact copy of the original column, only this one has hyperlinks. I can hide the original column, but now my spreadsheet just looks hokie. I can already hear people asking where the A column went.

Any ideas on how I can just turn the original column into hyperlinks without adding all that other crap?

edit: the reason I put C:\AwesomeStuff\ in a field on the spreadsheet is because that folder name might change....so I'd be able to change it in one place and have that fix all the links. I guess I could have put it in the CONCATENATE function like ".pdf", edited that when needed, then dragged it down the column to change it.....but I didn't.

 

[scarletmike]

Are you using 2007 (2008 on Mac), or an earlier version? I believe that just like in Word and Powerpoint, you can create a hyperlink that points to the document for each cell. To do that, all you should have to do is go to the Insert menu and choose "Hyperlink." You will probably get a dialog box (I'm doing this on 2008 for Mac, so you may have all this in that mega toolbar in the Windows version), and you can choose webpage, document, or e-mail address. For this you would choose document, but the hyperlink created is based upon it being a local file (i.e.) on your hard drive. If you're going to use this document across computers and people, you obviously need to have some way to serve up local files, which will affect the hyperlink address. Of course, that is a cell-by-cell process, and you seem to want to do this all at once (with 6500 entries, I don't blame you!). I don't know if you can do it all at once.

Of course, seeing as you went through all the trouble of using a formula for this, you probably tried the easy way already. So maybe you can disregard this post.

 

[MaxBuck]

I have a spreadsheet with 6500 entries.

Didn't need to go any further than this.

 

[BuckeyeNation27]

found a couple VB script websites that helped show me how to get it done. I can post the code here on Tuesday while I'm at work. It's probably terrible code....but it did the job.



here's the code:

Sub CreateHyper()
    Dim FolderPath As String
    Dim DotPDF As String
    Dim Combined As String
    
    FolderPath = "C:AwesomeStuff"
    DotPDF = ".pdf"
    
    For i = 2 To 6501
    Combined = FolderPath + Cells(i, 1) + DotPDF
    Cells(i, 1).Hyperlinks.Add Anchor:=Cells(i, 1), Address:=Combined
    Next
    
End Sub

There's probably a better way to do the FOR loop, since I'm sure there will be more than 6501 entries. Maybe some day I'll look that up.

 

[PlanetFrnd]

Links to external docs on your C drive?

 

[BuckeyeNation27]

it's not really on my C drive. my awesomestuff folder is housed on a hidden government server that only I and al gore have access to. i put C to fool you guys.

 

[Albert Gore Jr]

it's not really on my C drive. my awesomestuff folder is housed on a hidden government server that only I and al gore have access to. i put C to fool you guys.

About that...we really need to talk about those pictures of Carly Patterson you have on there. A date stamp of 2004 for someone born in 1988 is bad math for you, my man.

 

[BuckeyeNation27]

I was in Vietnam at the time. I think that makes her too old to be looked at.