Excel Help!

[CoopKill]

Ok, so I have gotten a bit further in my quest to make this happen, but not there yet. I have gotten my formula to this point, and am getting Invalid Name Errors.

=HYPERLINK(CONCATENATE(“\\P:\00_Insurance Jobs_OR_SW_WA\PO Lists\Kevin\PDF\”,A2,”.pdf”), A2)

No Excel gurus in this place?

[futurevr4man]

Well I was going to tell you to concatenate but you figured that out. I'm not understanding what exactly you are lacking right now? Is it that you want to spread this formula across the whole sheet instead of manually doing it a million times (exaggerating)?

I am assuming you know about using '$' in a row/column to have it always lookup that cell instead of reading others as you drag?

This is going to be difficult to solve over the Internet, even for a guru (which I'm not claiming to be)

[CoopKill]

This formula is "supposed" to work, but the Invalid Name Error is throwing me off. I tried it in 2 different network locations, as well as the C: drive on the comp. No deal.

I want a formula/macro to populate the cell with the hyperlink to a document in a folder with the name that is in another cell. I know I'm close, but can't figure out the error issue.

[futurevr4man]

im taking a stab at this because obviously i cant test my hypothesis...

the biggest issues i see now is you are hyperlinking a concatenated cell. thats why you are getting an invalid name error. if the name of the file is 'yes', right now you are hyperlinking 'A2yes'. that file doesnt exist. that should be your issue at hand right now.

with that being said, another confusion right now is, i dont understand what you want that cell that you click on to say. it looks like you wanted it to display "A2" but then you threw in that ".pdf"... im not sure that format within that hyperlink function will work at all. you an only have 2 arguments in that function... youre showing 3 (file destination, A2, and ".pdf").

if i can get my head wrapped solidly around what youre asking im quite sure i can help you figure it out, so please dont get frustrated with my confusion...



you want to click on a cell that says ____ and it will use a hyperlink from another cell (within the same workbook... different one...? same tab...?) and open the document associated with said hyperlink?


the way you phrased it in your previous post has got me tongue twisted

[futurevr4man]

wait wait wait... this formula:

=HYPERLINK(CONCATENATE(“\\P:\00_Insurance Jobs_OR_SW_WA\PO Lists\Kevin\PDF\”,A2,”.pdf”), A2)

its messed up, but i think i see what you want. you want the cell to display:

\\P:\00_Insurance Jobs_OR_SW_WA\PO Lists\Kevin\PDF\.pdf

and when you click it, it goes to the hyperlink? you gotta clear me up here billiam!

[CoopKill]

Yes the cell will link .pdf with the file name in front of the .pdf being the value of A2. The A2 at the end tells tge cell to display the value in the cell.

Sent from the Man Cave!

*\( ^o^ )/*

[futurevr4man]

this should be what you need as far as i understand

=HYPERLINK(CONCATENATE(A2,".pdf"))

A2 being the file name (in this case A2 should display \\P:\00_Insurance Jobs_OR_SW_WA\PO Lists\Kevin\PDF\ ).


if you want it to display something specific... lets say cell C4 says the word blah in it... then put this:

=HYPERLINK(CONCATENATE(A2,".pdf"),C4)

and the cell you put this into will display ----> blah

but will still hyperlink to your file that is found in A2

that should answer your questions unless im mistaken. if this doesnt, i would like to know

[CoopKill]

This is not that simple. That will not work as there is no formula in it that tells it the file name to link.

Example:

Cell A2 has a number in it, in this case A2 is populated with 100001. The formula I last have has ”,A2,”.pdf” in it which tells the formula to reference A2, take the data, and put in front of .pdf.

So I need the rest of the formula after =HYPERLINK, and before ”,A2,”.pdf”) Though I have found examples all over the place where this formula should work, I have not been able to get it to.

Recap: A2 has a Number, G2 needs a formula to look up A2 number attach it to .pdf, look in a specified path/folder, and create a hyperlink with the matching file found.

It may be that CONCATENATE cannot be used this way?, or there is an error in syntax of the formula, or a formula cannot do this, and it need to be done in VB...

[CoopKill]

Got it! WOOT!

Incorrect: =HYPERLINK(CONCATENATE(“\\P:\00_Insurance Jobs_OR_SW_WA\PO Lists\Kevin\PDF\”,A2,”.pdf”), A2)

Correct: =HYPERLINK(CONCATENATE(“\P:\00_Insurance Jobs_OR_SW_WA\PO Lists\Kevin\PDF\”,A2,”.pdf”), A2)

One backslash, and a week of smashing skull on desk... That's profitable!