J
Jeni Q
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
: 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
: ------------------------------------------------------------------------
----
:
:
:
:
: : > 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
: >
: >
: >
:
:
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
: 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
: ------------------------------------------------------------------------
----
:
:
:
:
: : > 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
: >
: >
: >
:
: