I am wanting to have Excel link to a website based on values in a column. For
example, 12345678 is the first value in column R:R, or R2. I want Excel to
convert these values to a link to
"http://www.somewhere.com/abscd.asp?addrs=12345678", but I only want to see
12345678 in that column.

Is this possible? Any help you provide is appreciated.

I am sure there is a solution in VB but if you want to use a
formula....

You could do it so that Column S contains the Hyperlink. In S2

=IF(R2=12345678,HYPERLINK("http://www.exceltip.com",R2),"")

In the HYPERLINK formula, the address http//... needs to be in quotes
if you are typing in the address. The R2 references the name that will
appear in the cell containing you link. Or you could use cell
references as the path name. Type in your address
http://www.exceltip.com in cell T2 say. Then your formula would be.

=IF(R2=12345678,HYPERLINK(T2,R2),"")

In either case, it still requires the user to click on the newly
created link to be forwarded to the site.



HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=497982

Thank you for responding, Steve. The solution you offered does work. However,
I neglected to mention that I am using an INDIRECT formula to pull the data
from a separate worksheet. In other words, my spreadsheet has numerous
worksheets. Worksheet A has all of the data. Worksheets B-F display and
reference data from Worksheet A to perform various formulas. Now, the data
from column S:S is populating in Worksheet B as an "http://..." but in plain
text, not a hyperlink. Is there anyway it can show in Worksheet B as a
clickable hyperlink?

"SteveG" wrote:


I am sure there is a solution in VB but if you want to use a
formula....

You could do it so that Column S contains the Hyperlink. In S2

=IF(R2=12345678,HYPERLINK("http://www.exceltip.com",R2),"")

In the HYPERLINK formula, the address http//... needs to be in quotes
if you are typing in the address. The R2 references the name that will
appear in the cell containing you link. Or you could use cell
references as the path name. Type in your address
http://www.exceltip.com in cell T2 say. Then your formula would be.

=IF(R2=12345678,HYPERLINK(T2,R2),"")

In either case, it still requires the user to click on the newly
created link to be forwarded to the site.



HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=497982

I created a sheet named A, B & C.

In Sheet A, I entered in cell A1 "B2". In sheet B, cell R2, I entered
=INDIRECT(A!A1) which returned the value in cell B2 of Sheet A which in
this case is 12345678.

Does the above describe basically what you are doing? If so.

In Sheet B, S2 I entered,

=IF(R2=12345678,HYPERLINK("http://www.exceltip.com",R2),"")

This returned a named hyperlink of 12345678 that when clicked opened
Exceltip.com.

I may be missing something. If you could provide your formulas and
data, I could take a look.

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=497982

Here is the formula I am using in cell E3 of Worksheet B:

=INDIRECT("'potentials'!s2")

This will pull from cell S2 in Worksheet A, which has this formula in it:

=HYPERLINK("http://www.somewhere.com/abscd.asp?addrs=" &R:R )

Column R:R has the 12345678 values in it from cell 2 to infinity.

I need for this to display as a clickable html link to that website in
Worksheet B.

Thanks again!

Norberto

"SteveG" wrote:


I created a sheet named A, B & C.

In Sheet A, I entered in cell A1 "B2". In sheet B, cell R2, I entered
=INDIRECT(A!A1) which returned the value in cell B2 of Sheet A which in
this case is 12345678.

Does the above describe basically what you are doing? If so.

In Sheet B, S2 I entered,

=IF(R2=12345678,HYPERLINK("http://www.exceltip.com",R2),"")

This returned a named hyperlink of 12345678 that when clicked opened
Exceltip.com.

I may be missing something. If you could provide your formulas and
data, I could take a look.

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=497982

Thanks, Steve. Please excuse if this creates a duplicate post, but I did not
see the one I sent in response to your most recent.

Here is the setup.

Workseet A has a set of values in R:R such as 12345678, which pertain to a
page such as http://www.somewhere.com/abscd.asp?addrs=12345678. R2 may
display 12345678 and R3 may have 23456789, etc to infinit y.

Worksheet A column S:S has this formula:

=HYPERLINK("http://www.somewhere.com/abscd.asp?addrs=" &R:R ). Which
displays a clickable link.

As I work the data in Worksheet A, I start in row 2 and work down then purge
or move the data so that row 3 becomes row 2, etc.

Worksheet B cell E3 has this formula in it:

=Worksheet A!S2

which displays http://www.somewhere.com/abscd.asp?addrs=12345678, or the
current record, but as plain text.

I need for this to be a clickable link in Worksheet B.

Thanks again!

Norberto

"SteveG" wrote:


I created a sheet named A, B & C.

In Sheet A, I entered in cell A1 "B2". In sheet B, cell R2, I entered
=INDIRECT(A!A1) which returned the value in cell B2 of Sheet A which in
this case is 12345678.

Does the above describe basically what you are doing? If so.

In Sheet B, S2 I entered,

=IF(R2=12345678,HYPERLINK("http://www.exceltip.com",R2),"")

This returned a named hyperlink of 12345678 that when clicked opened
Exceltip.com.

I may be missing something. If you could provide your formulas and
data, I could take a look.

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=497982

The HYPERLINK formula will not carry over to worksheet B, just the value
in 'potentials'!S2.

How about trying it this way.

In cell S2 of worksheet A, rather than the HYPERLINK formula, type in
your address as ="www.Exceltip.com"&R2. This will produce the site
address in text with the numeric value of R2 as the last part of the
address. (Assuming your site address always ends in the value that is
in column R).

In cell E3 of worksheet B, incorporate the HYPERLINK with the INDIRECT
formula like.

=HYPERLINK(INDIRECT("'Sheet A'!S2"),'Sheet A'!R2)

This will return the value in Sheet A!R2 as the name (12345678) to a
link at the address in SheetA!S2.

Does that solve it?


Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=497982

Thanks for writing, Steve. That formula returns an error. I have tried both
R2 & R:R with no luck. Does it matter that the website looks like this
"search.somewhere.com/abcdefg.asp?addrs=" and not a www.somewhere.com?

Thanks again.

Norberto

"SteveG" wrote:


The HYPERLINK formula will not carry over to worksheet B, just the value
in 'potentials'!S2.

How about trying it this way.

In cell S2 of worksheet A, rather than the HYPERLINK formula, type in
your address as ="www.Exceltip.com"&R2. This will produce the site
address in text with the numeric value of R2 as the last part of the
address. (Assuming your site address always ends in the value that is
in column R).

In cell E3 of worksheet B, incorporate the HYPERLINK with the INDIRECT
formula like.

=HYPERLINK(INDIRECT("'Sheet A'!S2"),'Sheet A'!R2)

This will return the value in Sheet A!R2 as the name (12345678) to a
link at the address in SheetA!S2.

Does that solve it?


Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=497982

Here's a correction to my last post. The address in sheet A S2 needs to
be typed in as ="https://your site here="&R2. Whati s the full address
of your link and I'll try it.

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=497982

Worked like a charm, Steve! Thanks for your help!

Norberto

"SteveG" wrote:


Here's a correction to my last post. The address in sheet A S2 needs to
be typed in as ="https://your site here="&R2. Whati s the full address
of your link and I'll try it.

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=497982

Glad I could help.

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=497982