Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create hyperlink from 2 cells, then open Word
Hello,
The purpose of this endeavour is to create a cell that when the user clicks on it, it will open up a Word file that THAT particular cell represents. FIRST STEP: Take the values from Column D (Tract Number), then add a ".doc" extension to it, then put the UNC filepath (\\server\data\reports) in front of all of that and put it in Column E. See me example below: File path plus Tract Number plus Extension \\server\data\reports\ 7-5-065-085 .doc .... to generate something like this: \\server\data\reports\7-5-065-085.doc SECOND STEP: Use the value (only when the user clicks on the hyperlink) from the cell in Column E and start up Word. Can this be done? Thanks in advance for all of your replies. Phil. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create hyperlink from 2 cells, then open Word
You may use HYPERLINK and string concatenation, like:
=HYPERLINK("\\server\data\reports\" & D1 & ".doc") That will create a link that the user can click. You can even substitute the text in quotes for other cell references. Hope this helps, Miguel. "Phil" wrote: Hello, The purpose of this endeavour is to create a cell that when the user clicks on it, it will open up a Word file that THAT particular cell represents. FIRST STEP: Take the values from Column D (Tract Number), then add a ".doc" extension to it, then put the UNC filepath (\\server\data\reports) in front of all of that and put it in Column E. See me example below: File path plus Tract Number plus Extension \\server\data\reports\ 7-5-065-085 .doc ... to generate something like this: \\server\data\reports\7-5-065-085.doc SECOND STEP: Use the value (only when the user clicks on the hyperlink) from the cell in Column E and start up Word. Can this be done? Thanks in advance for all of your replies. Phil. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create hyperlink from 2 cells, then open Word
In E1 enter:
=HYPERLINK("\server\data\reports\" & D1 & ".doc", "7-5-065-085") -- Gary's Student "Phil" wrote: Hello, The purpose of this endeavour is to create a cell that when the user clicks on it, it will open up a Word file that THAT particular cell represents. FIRST STEP: Take the values from Column D (Tract Number), then add a ".doc" extension to it, then put the UNC filepath (\\server\data\reports) in front of all of that and put it in Column E. See me example below: File path plus Tract Number plus Extension \\server\data\reports\ 7-5-065-085 .doc ... to generate something like this: \\server\data\reports\7-5-065-085.doc SECOND STEP: Use the value (only when the user clicks on the hyperlink) from the cell in Column E and start up Word. Can this be done? Thanks in advance for all of your replies. Phil. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create hyperlink from 2 cells, then open Word
Hi Miguel,
That gets me to the folder, but does not open the file in Word. Any other ideas? Phil "Miguel Zapico" wrote: You may use HYPERLINK and string concatenation, like: =HYPERLINK("\\server\data\reports\" & D1 & ".doc") That will create a link that the user can click. You can even substitute the text in quotes for other cell references. Hope this helps, Miguel. "Phil" wrote: Hello, The purpose of this endeavour is to create a cell that when the user clicks on it, it will open up a Word file that THAT particular cell represents. FIRST STEP: Take the values from Column D (Tract Number), then add a ".doc" extension to it, then put the UNC filepath (\\server\data\reports) in front of all of that and put it in Column E. See me example below: File path plus Tract Number plus Extension \\server\data\reports\ 7-5-065-085 .doc ... to generate something like this: \\server\data\reports\7-5-065-085.doc SECOND STEP: Use the value (only when the user clicks on the hyperlink) from the cell in Column E and start up Word. Can this be done? Thanks in advance for all of your replies. Phil. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create hyperlink from 2 cells, then open Word
I have done something similar (but not exactly this) in one of my
spreadsheets. First, the formula I use (warning, it's long) =IF(ISNA(LOOKUP(2,1/(1-ISBLANK(ColCount)))),"",HYPERLINK($B$1&LOOKUP(2,1/(1-ISBLANK(ColCount)),ColCount)&INDEX($B$2:$D$2,MATCH (LOOKUP(2,1/(1-ISBLANK(ColCount)),ColCount),ColCount,0)),LOOKUP(2 ,1/(1-ISBLANK(ColCount)),ColCount))) One of the differences is instead of just dealing with .doc extensions, I have .xls extensions and (just for the sake of having more than 2 types to deal with) .mdb extension. I have these in seperate columns and I use a dynamic named range to let me know how many columns I am dealing with. My named range looks something like this (shouldn't it look exactly like this???) =OFFSET(Links!$B9,,,1,COUNTA(Links!$2:$2)) The lookup part was something I adapted from previous posts on this forum that was originally was intended to show the last value in a column (or row, I can't remember) Mine just looks to see which of the 3 (or more) columns is populated so that the formula will know which extension to use. The UNC path I am using is in cell B1. The file extensions are in row 2. And what is displayed in the cell is the filename (no path or extension.) That seems to be the crux of it. Perhaps you can adapt it for your needs. -- Kevin Vaughn "Phil" wrote: Hello, The purpose of this endeavour is to create a cell that when the user clicks on it, it will open up a Word file that THAT particular cell represents. FIRST STEP: Take the values from Column D (Tract Number), then add a ".doc" extension to it, then put the UNC filepath (\\server\data\reports) in front of all of that and put it in Column E. See me example below: File path plus Tract Number plus Extension \\server\data\reports\ 7-5-065-085 .doc ... to generate something like this: \\server\data\reports\7-5-065-085.doc SECOND STEP: Use the value (only when the user clicks on the hyperlink) from the cell in Column E and start up Word. Can this be done? Thanks in advance for all of your replies. Phil. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create hyperlink from 2 cells, then open Word
Hello Gary,
That only gets me to the folder. It does not open up Word with that particular document, which is the second step that I need done. Any other ideas? Phil. "Gary''s Student" wrote: In E1 enter: =HYPERLINK("\server\data\reports\" & D1 & ".doc", "7-5-065-085") -- Gary's Student "Phil" wrote: Hello, The purpose of this endeavour is to create a cell that when the user clicks on it, it will open up a Word file that THAT particular cell represents. FIRST STEP: Take the values from Column D (Tract Number), then add a ".doc" extension to it, then put the UNC filepath (\\server\data\reports) in front of all of that and put it in Column E. See me example below: File path plus Tract Number plus Extension \\server\data\reports\ 7-5-065-085 .doc ... to generate something like this: \\server\data\reports\7-5-065-085.doc SECOND STEP: Use the value (only when the user clicks on the hyperlink) from the cell in Column E and start up Word. Can this be done? Thanks in advance for all of your replies. Phil. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create hyperlink from 2 cells, then open Word
It should open the word file, try to hard code the document name in the
formula, and check if that works. If so, you may then build that name in a different cell and use the formula over that cell, but the formula should work as it is if the document exists. Miguel. "Phil" wrote: Hi Miguel, That gets me to the folder, but does not open the file in Word. Any other ideas? Phil "Miguel Zapico" wrote: You may use HYPERLINK and string concatenation, like: =HYPERLINK("\\server\data\reports\" & D1 & ".doc") That will create a link that the user can click. You can even substitute the text in quotes for other cell references. Hope this helps, Miguel. "Phil" wrote: Hello, The purpose of this endeavour is to create a cell that when the user clicks on it, it will open up a Word file that THAT particular cell represents. FIRST STEP: Take the values from Column D (Tract Number), then add a ".doc" extension to it, then put the UNC filepath (\\server\data\reports) in front of all of that and put it in Column E. See me example below: File path plus Tract Number plus Extension \\server\data\reports\ 7-5-065-085 .doc ... to generate something like this: \\server\data\reports\7-5-065-085.doc SECOND STEP: Use the value (only when the user clicks on the hyperlink) from the cell in Column E and start up Word. Can this be done? Thanks in advance for all of your replies. Phil. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create hyperlink from 2 cells, then open Word
Hi Kevin,
In as much as I apprieciate your answer and its complexity, I was only able to grasp about 10% of what you said. Sadly, I wish I could use what you've provided me, but I wouldn't know where to begin (or end). If you could steer me a little further with some more hints or suggestions, I'd greatly apprieciate it! Phil. "Kevin Vaughn" wrote: I have done something similar (but not exactly this) in one of my spreadsheets. First, the formula I use (warning, it's long) =IF(ISNA(LOOKUP(2,1/(1-ISBLANK(ColCount)))),"",HYPERLINK($B$1&LOOKUP(2,1/(1-ISBLANK(ColCount)),ColCount)&INDEX($B$2:$D$2,MATCH (LOOKUP(2,1/(1-ISBLANK(ColCount)),ColCount),ColCount,0)),LOOKUP(2 ,1/(1-ISBLANK(ColCount)),ColCount))) One of the differences is instead of just dealing with .doc extensions, I have .xls extensions and (just for the sake of having more than 2 types to deal with) .mdb extension. I have these in seperate columns and I use a dynamic named range to let me know how many columns I am dealing with. My named range looks something like this (shouldn't it look exactly like this???) =OFFSET(Links!$B9,,,1,COUNTA(Links!$2:$2)) The lookup part was something I adapted from previous posts on this forum that was originally was intended to show the last value in a column (or row, I can't remember) Mine just looks to see which of the 3 (or more) columns is populated so that the formula will know which extension to use. The UNC path I am using is in cell B1. The file extensions are in row 2. And what is displayed in the cell is the filename (no path or extension.) That seems to be the crux of it. Perhaps you can adapt it for your needs. -- Kevin Vaughn "Phil" wrote: Hello, The purpose of this endeavour is to create a cell that when the user clicks on it, it will open up a Word file that THAT particular cell represents. FIRST STEP: Take the values from Column D (Tract Number), then add a ".doc" extension to it, then put the UNC filepath (\\server\data\reports) in front of all of that and put it in Column E. See me example below: File path plus Tract Number plus Extension \\server\data\reports\ 7-5-065-085 .doc ... to generate something like this: \\server\data\reports\7-5-065-085.doc SECOND STEP: Use the value (only when the user clicks on the hyperlink) from the cell in Column E and start up Word. Can this be done? Thanks in advance for all of your replies. Phil. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create hyperlink from 2 cells, then open Word
I apologize for the complexity of the solution. Based on the other replies
to you (though I guess they didn't work for you, but I'm not sure why not) you probably don't need such a complex solution. Unfortunately, I don't have internet access except at work, and, as busy as I've been lately, I usually can only pop in during lunch. And as it's past the time I am supposed to leave, I can't attempt to answer you now. If I get time during lunch tomorrow, and you still haven't got an answer that will work for you, I'll try breaking it down a little more. -- Kevin Vaughn "Phil" wrote: Hi Kevin, In as much as I apprieciate your answer and its complexity, I was only able to grasp about 10% of what you said. Sadly, I wish I could use what you've provided me, but I wouldn't know where to begin (or end). If you could steer me a little further with some more hints or suggestions, I'd greatly apprieciate it! Phil. "Kevin Vaughn" wrote: I have done something similar (but not exactly this) in one of my spreadsheets. First, the formula I use (warning, it's long) =IF(ISNA(LOOKUP(2,1/(1-ISBLANK(ColCount)))),"",HYPERLINK($B$1&LOOKUP(2,1/(1-ISBLANK(ColCount)),ColCount)&INDEX($B$2:$D$2,MATCH (LOOKUP(2,1/(1-ISBLANK(ColCount)),ColCount),ColCount,0)),LOOKUP(2 ,1/(1-ISBLANK(ColCount)),ColCount))) One of the differences is instead of just dealing with .doc extensions, I have .xls extensions and (just for the sake of having more than 2 types to deal with) .mdb extension. I have these in seperate columns and I use a dynamic named range to let me know how many columns I am dealing with. My named range looks something like this (shouldn't it look exactly like this???) =OFFSET(Links!$B9,,,1,COUNTA(Links!$2:$2)) The lookup part was something I adapted from previous posts on this forum that was originally was intended to show the last value in a column (or row, I can't remember) Mine just looks to see which of the 3 (or more) columns is populated so that the formula will know which extension to use. The UNC path I am using is in cell B1. The file extensions are in row 2. And what is displayed in the cell is the filename (no path or extension.) That seems to be the crux of it. Perhaps you can adapt it for your needs. -- Kevin Vaughn "Phil" wrote: Hello, The purpose of this endeavour is to create a cell that when the user clicks on it, it will open up a Word file that THAT particular cell represents. FIRST STEP: Take the values from Column D (Tract Number), then add a ".doc" extension to it, then put the UNC filepath (\\server\data\reports) in front of all of that and put it in Column E. See me example below: File path plus Tract Number plus Extension \\server\data\reports\ 7-5-065-085 .doc ... to generate something like this: \\server\data\reports\7-5-065-085.doc SECOND STEP: Use the value (only when the user clicks on the hyperlink) from the cell in Column E and start up Word. Can this be done? Thanks in advance for all of your replies. Phil. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create hyperlink from 2 cells, then open Word
Hi Kevin,
I don't know either why they didn't work. I have a feeling that it might require some kind of VBA event handler to make it work, but I'd be lying if I said that wasn't just a guess. I DO apprieciate your extra effort and am looking forward to hearing what you have to say, when you get a chance to do so. Phil. "Kevin Vaughn" wrote: I apologize for the complexity of the solution. Based on the other replies to you (though I guess they didn't work for you, but I'm not sure why not) you probably don't need such a complex solution. Unfortunately, I don't have internet access except at work, and, as busy as I've been lately, I usually can only pop in during lunch. And as it's past the time I am supposed to leave, I can't attempt to answer you now. If I get time during lunch tomorrow, and you still haven't got an answer that will work for you, I'll try breaking it down a little more. -- Kevin Vaughn "Phil" wrote: Hi Kevin, In as much as I apprieciate your answer and its complexity, I was only able to grasp about 10% of what you said. Sadly, I wish I could use what you've provided me, but I wouldn't know where to begin (or end). If you could steer me a little further with some more hints or suggestions, I'd greatly apprieciate it! Phil. "Kevin Vaughn" wrote: I have done something similar (but not exactly this) in one of my spreadsheets. First, the formula I use (warning, it's long) =IF(ISNA(LOOKUP(2,1/(1-ISBLANK(ColCount)))),"",HYPERLINK($B$1&LOOKUP(2,1/(1-ISBLANK(ColCount)),ColCount)&INDEX($B$2:$D$2,MATCH (LOOKUP(2,1/(1-ISBLANK(ColCount)),ColCount),ColCount,0)),LOOKUP(2 ,1/(1-ISBLANK(ColCount)),ColCount))) One of the differences is instead of just dealing with .doc extensions, I have .xls extensions and (just for the sake of having more than 2 types to deal with) .mdb extension. I have these in seperate columns and I use a dynamic named range to let me know how many columns I am dealing with. My named range looks something like this (shouldn't it look exactly like this???) =OFFSET(Links!$B9,,,1,COUNTA(Links!$2:$2)) The lookup part was something I adapted from previous posts on this forum that was originally was intended to show the last value in a column (or row, I can't remember) Mine just looks to see which of the 3 (or more) columns is populated so that the formula will know which extension to use. The UNC path I am using is in cell B1. The file extensions are in row 2. And what is displayed in the cell is the filename (no path or extension.) That seems to be the crux of it. Perhaps you can adapt it for your needs. -- Kevin Vaughn "Phil" wrote: Hello, The purpose of this endeavour is to create a cell that when the user clicks on it, it will open up a Word file that THAT particular cell represents. FIRST STEP: Take the values from Column D (Tract Number), then add a ".doc" extension to it, then put the UNC filepath (\\server\data\reports) in front of all of that and put it in Column E. See me example below: File path plus Tract Number plus Extension \\server\data\reports\ 7-5-065-085 .doc ... to generate something like this: \\server\data\reports\7-5-065-085.doc SECOND STEP: Use the value (only when the user clicks on the hyperlink) from the cell in Column E and start up Word. Can this be done? Thanks in advance for all of your replies. Phil. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create hyperlink from 2 cells, then open Word
I doubt if this is going to be substantively different than the solutions
proferred, but I changed my formula so that it only went to the .doc column. I started out this morning with Word not open and when I clicked on my link, word opened to my document. My file name is in column C (I think, let me paste my formula:) =HYPERLINK(B1&C27&C2,C27) Yes, UNC path (with trailing \) in B1, formula in A27, FileName in C27, friendly name is just the File name. Extension in C2 (.doc) HTH but given your previous problems, I am doubtful. Good luck. -- Kevin Vaughn "Phil" wrote: Hi Kevin, I don't know either why they didn't work. I have a feeling that it might require some kind of VBA event handler to make it work, but I'd be lying if I said that wasn't just a guess. I DO apprieciate your extra effort and am looking forward to hearing what you have to say, when you get a chance to do so. Phil. "Kevin Vaughn" wrote: I apologize for the complexity of the solution. Based on the other replies to you (though I guess they didn't work for you, but I'm not sure why not) you probably don't need such a complex solution. Unfortunately, I don't have internet access except at work, and, as busy as I've been lately, I usually can only pop in during lunch. And as it's past the time I am supposed to leave, I can't attempt to answer you now. If I get time during lunch tomorrow, and you still haven't got an answer that will work for you, I'll try breaking it down a little more. -- Kevin Vaughn "Phil" wrote: Hi Kevin, In as much as I apprieciate your answer and its complexity, I was only able to grasp about 10% of what you said. Sadly, I wish I could use what you've provided me, but I wouldn't know where to begin (or end). If you could steer me a little further with some more hints or suggestions, I'd greatly apprieciate it! Phil. "Kevin Vaughn" wrote: I have done something similar (but not exactly this) in one of my spreadsheets. First, the formula I use (warning, it's long) =IF(ISNA(LOOKUP(2,1/(1-ISBLANK(ColCount)))),"",HYPERLINK($B$1&LOOKUP(2,1/(1-ISBLANK(ColCount)),ColCount)&INDEX($B$2:$D$2,MATCH (LOOKUP(2,1/(1-ISBLANK(ColCount)),ColCount),ColCount,0)),LOOKUP(2 ,1/(1-ISBLANK(ColCount)),ColCount))) One of the differences is instead of just dealing with .doc extensions, I have .xls extensions and (just for the sake of having more than 2 types to deal with) .mdb extension. I have these in seperate columns and I use a dynamic named range to let me know how many columns I am dealing with. My named range looks something like this (shouldn't it look exactly like this???) =OFFSET(Links!$B9,,,1,COUNTA(Links!$2:$2)) The lookup part was something I adapted from previous posts on this forum that was originally was intended to show the last value in a column (or row, I can't remember) Mine just looks to see which of the 3 (or more) columns is populated so that the formula will know which extension to use. The UNC path I am using is in cell B1. The file extensions are in row 2. And what is displayed in the cell is the filename (no path or extension.) That seems to be the crux of it. Perhaps you can adapt it for your needs. -- Kevin Vaughn "Phil" wrote: Hello, The purpose of this endeavour is to create a cell that when the user clicks on it, it will open up a Word file that THAT particular cell represents. FIRST STEP: Take the values from Column D (Tract Number), then add a ".doc" extension to it, then put the UNC filepath (\\server\data\reports) in front of all of that and put it in Column E. See me example below: File path plus Tract Number plus Extension \\server\data\reports\ 7-5-065-085 .doc ... to generate something like this: \\server\data\reports\7-5-065-085.doc SECOND STEP: Use the value (only when the user clicks on the hyperlink) from the cell in Column E and start up Word. Can this be done? Thanks in advance for all of your replies. Phil. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a hyperlink to a web page in Excel? | Excel Discussion (Misc queries) | |||
how do you get Word to open documents in it's own window(s)? | Excel Discussion (Misc queries) | |||
How do I use 3 cells to create the string for a lookup function? | Excel Worksheet Functions | |||
Hyperlink to a word document | Excel Discussion (Misc queries) | |||
Hyperlink to word document problem | Links and Linking in Excel |