I'd like to take the values (numbers) in a particular column to a hyperlink
in which the number is part of the url. For example, imagine a col of 5
digit #s. In cell A:2 is 12345. I want to change this to a hyperlink to the
url http://www.url.com/id=12345. There has to be a way but I've never done
it. Thanks!

Just concatenate the constant with the cell value.

Ross


"Big UT Fan" <(E-Mail Removed)> wrote in message
news:7F9E518F-A407-464D-AD98-(E-Mail Removed)...
> I'd like to take the values (numbers) in a particular column to a
> hyperlink
> in which the number is part of the url. For example, imagine a col of 5
> digit #s. In cell A:2 is 12345. I want to change this to a hyperlink to
> the
> url http://www.url.com/id=12345. There has to be a way but I've never done
> it. Thanks!

I tried to use the HYPERLINK function but am getting a circular reference. In
cell B2 I inserted the following funciton:
=HYPERLINK(CONCATENATE("http://www.foo.com/id=",B2))

What am I doing wrong?


"Ross Culver" wrote:

> Just concatenate the constant with the cell value.
>
> Ross
>
>
> "Big UT Fan" <(E-Mail Removed)> wrote in message
> news:7F9E518F-A407-464D-AD98-(E-Mail Removed)...
> > I'd like to take the values (numbers) in a particular column to a
> > hyperlink
> > in which the number is part of the url. For example, imagine a col of 5
> > digit #s. In cell A:2 is 12345. I want to change this to a hyperlink to
> > the
> > url http://www.url.com/id=12345. There has to be a way but I've never done
> > it. Thanks!
>
>
>

Thanks Guys. I'm confident what you put here with the Dim statement will
work but as I've never actually programmed in Excel I'm kind of lost here as
to what to do with this. Any pointers/tips?

"Sandusky" wrote:

>
> "Big UT Fan" <(E-Mail Removed)> wrote in message
> news:7F9E518F-A407-464D-AD98-(E-Mail Removed)...
> > I'd like to take the values (numbers) in a particular column to a
> > hyperlink
> > in which the number is part of the url. For example, imagine a col of 5
> > digit #s. In cell A:2 is 12345. I want to change this to a hyperlink to
> > the
> > url http://www.url.com/id=12345. There has to be a way but I've never done
> > it. Thanks!
>
> Dim tx as String
> tx = "http://www.url.com/id=" & Range("A2").Value
>
>
>
>
>

You are trying to assign to B2 some piece of text and the contents of B2...
you can't do that (hence, the circular reference error). B2 either contains
a value or a formula, it can't have both. Either put the formula in another
cell (say, C2) or you will need to use a macro of some sort if you want to
change the value in place.

Rick


"Big UT Fan" <(E-Mail Removed)> wrote in message
news:37A9CDF5-95A5-4FA1-9815-(E-Mail Removed)...
>I tried to use the HYPERLINK function but am getting a circular reference.
>In
> cell B2 I inserted the following funciton:
> =HYPERLINK(CONCATENATE("http://www.foo.com/id=",B2))
>
> What am I doing wrong?
>
>
> "Ross Culver" wrote:
>
>> Just concatenate the constant with the cell value.
>>
>> Ross
>>
>>
>> "Big UT Fan" <(E-Mail Removed)> wrote in message
>> news:7F9E518F-A407-464D-AD98-(E-Mail Removed)...
>> > I'd like to take the values (numbers) in a particular column to a
>> > hyperlink
>> > in which the number is part of the url. For example, imagine a col of
>> > 5
>> > digit #s. In cell A:2 is 12345. I want to change this to a hyperlink
>> > to
>> > the
>> > url http://www.url.com/id=12345. There has to be a way but I've never
>> > done
>> > it. Thanks!
>>
>>
>>

So Rick...Are you saying that I still won't be able to do it using VBA as
suggested by Sandusky in the next reply?


"Rick Rothstein (MVP - VB)" wrote:

> You are trying to assign to B2 some piece of text and the contents of B2...
> you can't do that (hence, the circular reference error). B2 either contains
> a value or a formula, it can't have both. Either put the formula in another
> cell (say, C2) or you will need to use a macro of some sort if you want to
> change the value in place.
>
> Rick
>
>
> "Big UT Fan" <(E-Mail Removed)> wrote in message
> news:37A9CDF5-95A5-4FA1-9815-(E-Mail Removed)...
> >I tried to use the HYPERLINK function but am getting a circular reference.
> >In
> > cell B2 I inserted the following funciton:
> > =HYPERLINK(CONCATENATE("http://www.foo.com/id=",B2))
> >
> > What am I doing wrong?
> >
> >
> > "Ross Culver" wrote:
> >
> >> Just concatenate the constant with the cell value.
> >>
> >> Ross
> >>
> >>
> >> "Big UT Fan" <(E-Mail Removed)> wrote in message
> >> news:7F9E518F-A407-464D-AD98-(E-Mail Removed)...
> >> > I'd like to take the values (numbers) in a particular column to a
> >> > hyperlink
> >> > in which the number is part of the url. For example, imagine a col of
> >> > 5
> >> > digit #s. In cell A:2 is 12345. I want to change this to a hyperlink
> >> > to
> >> > the
> >> > url http://www.url.com/id=12345. There has to be a way but I've never
> >> > done
> >> > it. Thanks!
> >>
> >>
> >>
>
>

Yes you can use VBA (that is what I meant by "or you will need to use a
macro of some sort if you want to change the value in place"). The point of
my response dealt with this statement of yours...

> In cell B2 I inserted the following funciton:
> =HYPERLINK(CONCATENATE("http://www.foo.com/id=",B2))

Placing a formula referring to B2 in the cell B2 is what caused your
circular error. VBA (or a macro if you will) can write directly into a cell
because it is not "in" the cell itself (like a formula on the worksheet is);
rather, it is something that simply has the ability to act on a worksheet in
whole or in part.

Rick


"Big UT Fan" <(E-Mail Removed)> wrote in message
news:0F591D36-ED1C-49EA-93B8-(E-Mail Removed)...
>
> So Rick...Are you saying that I still won't be able to do it using VBA as
> suggested by Sandusky in the next reply?
>
>
> "Rick Rothstein (MVP - VB)" wrote:
>
>> You are trying to assign to B2 some piece of text and the contents of
>> B2...
>> you can't do that (hence, the circular reference error). B2 either
>> contains
>> a value or a formula, it can't have both. Either put the formula in
>> another
>> cell (say, C2) or you will need to use a macro of some sort if you want
>> to
>> change the value in place.
>>
>> Rick
>>
>>
>> "Big UT Fan" <(E-Mail Removed)> wrote in message
>> news:37A9CDF5-95A5-4FA1-9815-(E-Mail Removed)...
>> >I tried to use the HYPERLINK function but am getting a circular
>> >reference.
>> >In
>> > cell B2 I inserted the following funciton:
>> > =HYPERLINK(CONCATENATE("http://www.foo.com/id=",B2))
>> >
>> > What am I doing wrong?
>> >
>> >
>> > "Ross Culver" wrote:
>> >
>> >> Just concatenate the constant with the cell value.
>> >>
>> >> Ross
>> >>
>> >>
>> >> "Big UT Fan" <(E-Mail Removed)> wrote in message
>> >> news:7F9E518F-A407-464D-AD98-(E-Mail Removed)...
>> >> > I'd like to take the values (numbers) in a particular column to a
>> >> > hyperlink
>> >> > in which the number is part of the url. For example, imagine a col
>> >> > of
>> >> > 5
>> >> > digit #s. In cell A:2 is 12345. I want to change this to a
>> >> > hyperlink
>> >> > to
>> >> > the
>> >> > url http://www.url.com/id=12345. There has to be a way but I've
>> >> > never
>> >> > done
>> >> > it. Thanks!
>> >>
>> >>
>> >>
>>
>>

try this:

Sub LinkMe()

Range("a2").Select
For x = 1 To ActiveSheet.UsedRange.Rows.Count
tx = "http://www.url.com/id=" & ActiveCell.Value
ActiveCell.Formula = "=hyperlink(""" & tx & """)"
ActiveCell.Offset(1, 0).Select
Next x

End Sub

Given the list of data you want hyperlinked is continuous and it starts in
cell A2. I don't like using Select but it's simple code to get you started.


--
MacGuy


"Big UT Fan" wrote:

> Thanks Guys. I'm confident what you put here with the Dim statement will
> work but as I've never actually programmed in Excel I'm kind of lost here as
> to what to do with this. Any pointers/tips?
>
> "Sandusky" wrote:
>
> >
> > "Big UT Fan" <(E-Mail Removed)> wrote in message
> > news:7F9E518F-A407-464D-AD98-(E-Mail Removed)...
> > > I'd like to take the values (numbers) in a particular column to a
> > > hyperlink
> > > in which the number is part of the url. For example, imagine a col of 5
> > > digit #s. In cell A:2 is 12345. I want to change this to a hyperlink to
> > > the
> > > url http://www.url.com/id=12345. There has to be a way but I've never done
> > > it. Thanks!
> >
> > Dim tx as String
> > tx = "http://www.url.com/id=" & Range("A2").Value
> >
> >
> >
> >
> >

Do you mean you want to change the cell to become a Hyperlink? So that you
can click on the cell and jump to the link?

Dim Cell As Range

For Each Cell In Range("A1:A9")
ActiveSheet.Hyperlinks.Add _
Anchor:=Cell, _
Address:="http://www.url.com/id=" & Cell.Value, _
SubAddress:="", _
TextToDisplay:="http://www.url.com/id=" & Cell.Value, _
ScreenTip:="Click Here to go to http://www.url.com/id=" & Cell.Value
Next Cell


"Big UT Fan" wrote:

> I'd like to take the values (numbers) in a particular column to a hyperlink
> in which the number is part of the url. For example, imagine a col of 5
> digit #s. In cell A:2 is 12345. I want to change this to a hyperlink to the
> url http://www.url.com/id=12345. There has to be a way but I've never done
> it. Thanks!