Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel hyperlink text to display and vlookup
Good Day,
hope that you possibly can help me. I tried below function in an Excel sheet. It works well until I change the text to display for the hyperlink, for e.g. to cnn instead of http://www.cnn.com. Then the link doesnt work anymore. For short links this is no issue, but unfortunately I have major long links to follow and would love to make them more user friendly by shortening the displayed text. Is this possible at all? I seriously appreciate your help. Thank you and best regards, Kerry Start with a table in M1 thru N5: 1 http://www.cnn.com 2 http://www.abc.com 3 http://www.nbc.com 4 http://www.cbs.com 5 http://www.fox.com In A1 put your data validation dropdown covering the list in column M In B1 put: =HYPERLINK(VLOOKUP(A1,M1:N5,2,FALSE)) The VLOOKUP picks the right link and the HYPERLINK function makes the result "click-able". Enjoy. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel hyperlink text to display and vlookup
By the way what is your lookup value (A1). Is that a friendly name like cnn
or so . If so try the below =HYPERLINK(VLOOKUP(A1,M1:N5,2,FALSE),A1) OR have a friendly name for each in Col O1:O5 and then =HYPERLINK(VLOOKUP(A1,M1:O5,2,FALSE),VLOOKUP(A1,M1 :O5,3,FALSE)) If this post helps click Yes --------------- Jacob Skaria "bluker" wrote: Good Day, hope that you possibly can help me. I tried below function in an Excel sheet. It works well until I change the text to display for the hyperlink, for e.g. to cnn instead of http://www.cnn.com. Then the link doesnt work anymore. For short links this is no issue, but unfortunately I have major long links to follow and would love to make them more user friendly by shortening the displayed text. Is this possible at all? I seriously appreciate your help. Thank you and best regards, Kerry Start with a table in M1 thru N5: 1 http://www.cnn.com 2 http://www.abc.com 3 http://www.nbc.com 4 http://www.cbs.com 5 http://www.fox.com In A1 put your data validation dropdown covering the list in column M In B1 put: =HYPERLINK(VLOOKUP(A1,M1:N5,2,FALSE)) The VLOOKUP picks the right link and the HYPERLINK function makes the result "click-able". Enjoy. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel hyperlink text to display and vlookup
Good Day,
this was easy. I tried both solutions and they both work perfectly. Thank you so much! Kerry "Jacob Skaria" wrote: By the way what is your lookup value (A1). Is that a friendly name like cnn or so . If so try the below =HYPERLINK(VLOOKUP(A1,M1:N5,2,FALSE),A1) OR have a friendly name for each in Col O1:O5 and then =HYPERLINK(VLOOKUP(A1,M1:O5,2,FALSE),VLOOKUP(A1,M1 :O5,3,FALSE)) If this post helps click Yes --------------- Jacob Skaria "bluker" wrote: Good Day, hope that you possibly can help me. I tried below function in an Excel sheet. It works well until I change the text to display for the hyperlink, for e.g. to cnn instead of http://www.cnn.com. Then the link doesnt work anymore. For short links this is no issue, but unfortunately I have major long links to follow and would love to make them more user friendly by shortening the displayed text. Is this possible at all? I seriously appreciate your help. Thank you and best regards, Kerry Start with a table in M1 thru N5: 1 http://www.cnn.com 2 http://www.abc.com 3 http://www.nbc.com 4 http://www.cbs.com 5 http://www.fox.com In A1 put your data validation dropdown covering the list in column M In B1 put: =HYPERLINK(VLOOKUP(A1,M1:N5,2,FALSE)) The VLOOKUP picks the right link and the HYPERLINK function makes the result "click-able". Enjoy. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I display the text of a hyperlink? | Excel Worksheet Functions | |||
hyperlink display text | Excel Discussion (Misc queries) | |||
How do I display hyperlink as embedded jump text vice friendly tex | Excel Discussion (Misc queries) | |||
Hyperlink display text maximum in Excel 2003 | Excel Discussion (Misc queries) | |||
How do I display hyperlink in Excel cell? | Excel Discussion (Misc queries) |