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:
I've stepped through a formula evaluation and here's what I learned.
+ First it translates CELL("filename",$A$1) into
+ Then it changes the second CELL("filename",$A$1) into
+ Next it tries to translate FIND("[",T:GeddesResumesreslist.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
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
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,
"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
: in cell B2 i put the following formula:-
: 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:-
: Now assuming your names start A2, and that you want the links in say Col
: in D2 put the following formula and copy down as far as needed:-
: This should give you a list of hyperlinks in about 30 seconds vs doing
: one by one in 6 hours or so.
: 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
: > 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
: > 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
: > because that's where the files are). The Text to Display: defaults to
: > 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
: > replaced by a hyperlink to the corresponding file in the same folder.
: > 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
: > 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