Using the Hyperlink Function and finding filenames
 

Ken,
Wow, thanks!!

Unfortunately, I'm having a small problem. I think I've followed your
instructions to the word, but I'm getting a VALUE! error in B1 where I've
entered the filepath formula.

Here is the current formula:
=MID(CELL("filename",$A$1),1,FIND("[",CELL("filename",$A$1))-1)
I've stepped through a formula evaluation and here's what I learned.
+ First it translates CELL("filename",$A$1) into
T:\Geddes\Resumes\reslist.csv.
+ Then it changes the second CELL("filename",$A$1) into
T:\Geddes\Resumes\reslist.csv.
+ Next it tries to translate FIND("[",T:\Geddes\Resumes\reslist.csv) but
winds up with #VALUE!

I hope that makes sense. I'm not sure what's going on. I don't quite
understand the purpose of the open bracket in the FIND statement.

So then I deleted the filepath formula and the hyperlink formula
=HYPERLINK(A2,A2)
worked! I just copied the formula down the column and it worked just fine.
Is that because the default file location for a hyperlink is in the current
folder?
The only problem with this method is that I have to leave column A intact. I
can't delete it, which I'd like to do because it's redundant to have two
columns that have the exact same text in them.

So, any advice?
Thanks a lot,
Jennifer


"Ken Wright" wrote in message
...
: One way is to use the hyperlink function =HYPERLINK()
:
: Assume I have an mp3 file for example in the folder D:\4mydata called
: wow.mp3 and in an excel file I have that text of wow.mp3 in say A2. Then
if
: in cell B2 i put the following formula:-
:
: =HYPERLINK("D:\4mydata\"&A2,A2)
:
: then I can now click on B2 and it will link straight to the file. I can
: also use a formula to get me the filepath and then use that in the formula
: as opposed to hardwiring in the path.
:
:
: In your scenario, assuming the Excel file is in the SAME folder, then
: assuming all your filenames are in Col A, starting A2, put the following
: formula in cell B1:-
:
: =MID(CELL("filename",$A$1),1,FIND("[",CELL("filename",$A$1))-1)
:
: Now assuming your names start A2, and that you want the links in say Col
D,
: in D2 put the following formula and copy down as far as needed:-
:
: =HYPERLINK($B$1&A2,A2)
:
: This should give you a list of hyperlinks in about 30 seconds vs doing
them
: one by one in 6 hours or so.
:
: --
: Regards
: Ken....................... Microsoft MVP - Excel
: Sys Spec - Win XP Pro / XL 97/00/02/03
:
: ------------------------------*------------------------------*------------
----
: It's easier to beg forgiveness than ask permission :-)
: ------------------------------*------------------------------*------------
----
:
:
:
:
: "Jeni Q" wrote in message
: ...
: Greetings,
:
: I have a user who is doing a repetitive action in a spreadsheet
: and I'm wondering if there's a better way to do what she's doing. Let me
: explain the process.
:
: Sue has a folder that contains 250-300 *.PDF files that have a naming
: convention like such: GR005843.pdf. In this folder she also has .xls
file
: that contains fields like Filename, Date, Last Name, etc.
: Each file listed in the Filename column corresponds to a PDF file in the
: folder and displays the exact title of the file, e.g. "GR005843.pdf".
: Currently, she goes to the first data cell in the column, A2, and
inserts
: a
: hyperlink into the XLS file by right-clicking and choosing Hyperlink. In
: the
: Insert Hyperlink dialog box, Link to: Existing File or Web Page is
: automatically selected. Look in: defaults to Current Folder (which is
good
: because that's where the files are). The Text to Display: defaults to
the
: text that is already in the cell. She types in the file name
: (GR005843.pdf)
: in the Address field and chooses OK. Now the text that was in that cell
is
: replaced by a hyperlink to the corresponding file in the same folder.
Then
: she moves to A3 and repeats the same steps. She does this about 250-300
: times, depending on how many files are in the monthly batch.
:
: I feel like there should be a better way to do this but can't figure out
: how
: a recorded macro could do so. It's the getting to the next cell part
that
: confuses me. I'm guessing it'll take some VBA code, but I'm not well
: versed
: in that. Can anyone provide some suggestions or advice for me? Can you
: tell
: me if what I'm trying to do is impossible?
:
: I'll be happy to answer any questions or clarify something I did not
: explain
: well.
: Thanks in advance for your help.
:
: Jeni Q
:
:
:
:
: