------ Register to get rid of these "In Post" ads! ------


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 doesn’t 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.

------ Register to get rid of these "In Post" ads! ------


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 doesn’t 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.
>

------ Register to get rid of these "In Post" ads! ------


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 doesn’t 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.
> >