-
Excel Help!
I am putting together an excel spreadsheet that does what the stupid expensive programs cannot that we have at work.
I know how to do everything I need to but for one thing. I need a hyperlink to a file on the network that is named the same as a cell. I have searched everywhere, and other than going to school for Visual Basic the only thing I have came up with is this:
=HYPERLINK("\\P:\00_Insurance Jobs_OR_SW_WA\PO Lists\Kevin\PDF\100001.pdf", A2)
That works great, but I need each cell displaying the hyperlink to auto input the number in front of the .pdf which in this case is the A2 cell.
Or another idea to accomplish creating a link in a cell that searches a given network folder for a file name matching a cells contents.
Ideas???
-
shouldn't need "hyperlink"
-
Without the "Hyperlink" you lose the friendly name, and get the full path. No good. As I said this works, but I need a formula/script, or something to get it working in 15 thousand cells.
-
-
Here is an example of a balance of an account off of one spread sheet linked to another spreadsheet that lists all the accounts
='C:\Users\PappBear\Dropbox\Church Financials\2014\[CHECKING ACCOUNT(2014).xlsx]MARCH'!$E$8
so
='path that the spreadsheet is located\[name of spreadsheet]TAB NAME AT THE BOTTOM OF THE SPREADSHEET'!$COLUMN LETTER$ROW NUMBER
-
OK Above my knowledge, sorry
-
Yeah mine as well. I have 11 workbooks all linked to a master, and a bunch of other crap. I just need the ability to assign document copies to a link in cells without manually doing it.
-
I have 8 spreadsheets that I use for balancing the Church accounts, I just established each link individually as needed as I designed them.
-
This is a PO system: 15 users, 1000 allotted numbers each, and a master with linked sheets to each for the controller to access at anytime for data entry into other programs.
I have pads setup in the field so all they need to do is snap picture, and know job name. The pad converts to pdf (needed for another program), and emails to the superintendent where he/she adds PO #/info, saves the pdf on the network in a folder where I need excel to look, and marry each pdf to the row via hyperlink. Simple right?
-
-
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?
-
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)
-
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.
-
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
-
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!
-
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^ )/*
-
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 :)
-
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...
-
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! :p
