create hyperlink in one cell with URL in a different cell

[confused on the tundra]

I need to create a hyperlink to a URL address that is in another cell
A1:A10 have items numbers and B1:B10 have the corrosponding web page that
has a description of that item, i.e. B1 has a URL to a describtion of the
item in A1.
I wan to have A1:A10 be hyperlinked to the corrasponding link in B1:B10. I
have tried copying the URL address in B1, highlighting
A1-->insert-->hyperlink, but I can not paste the address into the box that
asks for the address. I though I could do a macro to do all of them but
apparentlly not. I have over a thousand to do, is there a automated way to do
this?

 

[Peo Sjoblom]

If you want cell A1 to have a hyperlink direct to the web address in B1 you
need to select existing file or webpage in the dialogue window that opens.
You can also use the hyperlink formula and refer to the web address in the
adjacent cells, that would probably be faster for multiple links since you
could just copy it down

=HYPERLINK(B1,12345)

where 12345 is the item number


Regards,

Peo Sjoblom

 

[confused on the tundra]

The item number in A1 is generated by a concetenate, so when I do the
hyperlink formula it comes up with an error.

 

[Dave Peterson]

But what's in B1?

maybe you need:
=hyperlink("http://" & b1, "Click Me")

I'm not sure how A1 got into your formula from Peo's recommendation, though.

 

[confused on the tundra]

Here is what I have in my sheet, sorry if I was too confused to understand
the help offered.
A1 displays 12345A but in the formula box is =concatenate(B1,B2)
B1 has 12345 in it
C1 has "A" in it
D1 displays in the cell "http://www.microsoft.com/12345A/item_description
the formula box has =concatenate(E1,A1,F1)
E1 has http://www.microsoft.com/
F1 has /item_description

some item numbers have 5 numbers, some have six, and some have letters. I
did the concatenate so I could sort and keep the items in common sense order.

I did it that way so the item numbers would be inserted into the web
address. So now I have the item numbers in A1:A1000 as a result of the
concatenate function on B1:B1000 and C1:C1000 and the complete web address
for the item description in D11000 as a result of the concatenate function
using E1:E100 and F1:F1000.


I want to hyperlink the item number in A1:A1000 the the corrosponding web
addresses in D11000. When the suggestion was made to use "hyperlink formula
and refer to the web address in the adjacent cells, that would probably be
faster for multiple links since you could just copy it down". I thought this
meant to enter the formula in the A1 cell. I was mistaken obvoiusly. So how
would I use the hyperlink formula to refer to the address in a different
cell?

Sorry for the confusion and thanks for the help.

 

[Dave Peterson]

First you wrote =concatenate(b1,b2), but did you really mean
=concatenate(b1,c1)

And second, I'm kind of lazy. I like just using & to concatenate strings:
=b1&c1
works the same way as =concatenate(b1,c1)

So the results of the formula in D1 is what should be hyperlinked to?

If yes, I could use another cell (G1??):
=hyperlink(d1,"click me to go there!")

or I could build that string into the hyperlink:
=hyperlink(e1&a1&f1,"Click me to go there!")

(This sample wasn't a working link, right?)

======
One word of warning.

If your number always has to have six digits (leading zeros???) in it, then
maybe:

In A1:
=text(b1,"000000")&c1

 

[confused on the tundra]

Yes, I did mean to type Concatenate(B1,C1) and it was a made-up site address.
I did add a column and added an appropreate phrase and it works fine. What I
was hoping to do is addtach the hyperlink to the item number in A1 however,
e.g. click on the item number and go directly to the web page. Still this is
a fine solution and I thank you for your help. Oh and thanks for the tip
about using "&" instead of writing out concatenate everytime.

Merry Christmas to all

 

[Dave Peterson]

There's no reason you couldn't build the hyperlink formula in A1.

=hyperlink(e1&b1&c1&f1,b1&c1)

You can build a pretty long formula without using those helper cells.

Yes, I did mean to type Concatenate(B1,C1) and it was a made-up site address.
I did add a column and added an appropreate phrase and it works fine. What I
was hoping to do is addtach the hyperlink to the item number in A1 however,
e.g. click on the item number and go directly to the web page. Still this is
a fine solution and I thank you for your help. Oh and thanks for the tip
about using "&" instead of writing out concatenate everytime.

Merry Christmas to all

 

[confused on the tundra]

OK I got it now. I thought if I removed the B1&C1 from the formula bar for A1
I would lose the ability to sort, but I finally got what you were saying and
it works great. Now if I remember it when I get back to work next week!
Thanks Dave for your patience.

 

[Dave Peterson]

Glad to help.

OK I got it now. I thought if I removed the B1&C1 from the formula bar for A1
I would lose the ability to sort, but I finally got what you were saying and
it works great. Now if I remember it when I get back to work next week!
Thanks Dave for your patience.