Hyperlink to the meat of a cell

[Pierre]

This would be easier for all of us if I knew the right terminology but here
goes nothing. I have an excel spreadsheet with approx 5000 part numbers. I am
trying to make a hyperlink for each of those part numbers without having to
actually go to each site, copy the link and make my hyperlink from there. The
hyperlinks are all identical except for the part number. I will use Snap On
as an example. My part numbers are in column B. The actual link is for the
first part number in B1 is
http://buy1.snapon.com/catalog/item...1&group_ID=898&store=snapon-store&dir=catalog.
The only part of this link that would need to change for the next Hyperlink
is 87381. I was hoping that in column C, I could make a formula using the
Hyperlink function and insert B1 instead of 87381 . From there I was hoping
to copy and paste the formula all the way down the 5000 part numbers and that
the B1 would automatically change from B1 to B2, B3.... Instead, B2 actually
shows up as B2 in the formula and I end up at Snap On Home and not the actual
toll I am after.
WOW, I hope this makes sense to somebody out there.
Thanks

 

[JLatham]

The formula you want would be:

="http://buy1.snapon.com/catalog/item...1&group_ID=898&store=snapon-store&dir=catalog"
& B1 & "&group_ID=898&store=snapon-store&dir=catalog"

all on one line of course.

Here's a macro that would do the whole job for you, without using column C
at all:

Sub MakeTheHyperlinks()
'remember that these 1st 2 should all be on one line
'you'll have to edit after copying into your workbook.
Const leftPart =
"http://buy1.snapon.com/catalog/item...1&group_ID=898&store=snapon-store&dir=catalog"
Const rightPart = "&group_ID=898&store=snapon-store&dir=catalog"

Const colWithPN = "B"
Const firstPNRow = 1

Dim pnRange As Range
Dim anyPN As Range
Dim theHLink As String

Set pnRange = ActiveSheet.Range(colWithPN & firstPNRow & _
":" & ActiveSheet.Range(colWithPN & Rows.Count).End(xlUp).Address)
Application.ScreenUpdating = False
For Each anyPN In pnRange
theHLink = leftPart & Trim(Str(anyPN.Value)) & rightPart
ActiveSheet.Hyperlinks.Add Anchor:=anyPN, Address:=theHLink
Next
Set pnRange = Nothing
End Sub

 

[Pierre]

Hey J,
First and foremost, thanks for the help

Unfortunately, I still have a problem, below are the formulas or links that
I ended up with using what you suggested. The top one works perfect. The next
two, which were copied from the first and pasted on row 2 and 3, look exactly
like what I wanted to do however when I click on the link, it sends me to the
same tool as the one in the top row. When I put the cursor on link 2 and 3
and leave it there for a moment, it shows the actual hyperlink and all three
hyperlinks are the same.
By the way, it may be obvious to you but just in case, the numbers you see
before the links below, are the numbers in column B.

87381 http://buy1.snapon.com/catalog/item...81&group_ID=898&store=snapon-store&dir=catalo
7585 http://buy1.snapon.com/catalog/item...85&group_ID=898&store=snapon-store&dir=catalo
7641 http://buy1.snapon.com/catalog/item...1&group_ID=898&store=snapon-store&dir=catalog

I really hate to have to admit this but for your second suggestion, you are
way over my head

 

[Pierre]

Well, I found a way that works for me. I assume that the Excel purists will
probably find it messy but like I said it works.
Once again, I would like to thank JLatham, he got me on the right track.

I will try to explain this to the best of my limited abilities computer
skills.

As I explained in the original question, I had thousands of part numbers
that I was hoping to hyperlink to directly to a web site without having to do
each one individually and than save the hyperlink. I needed a formula of some
kind.

I needed this for work and the catalogue is not available from internet so I
used Snap-on as an example and it works the same.

First thing I did was to look up part number 87381 on the Snap-on site and
copied the address.
http://buy1.snapon.com/catalog/item...1&group_ID=898&store=snapon-store&dir=catalog

In cell B1, I pasted the first part of the address up to and including the
equal sign
http://buy1.snapon.com/catalog/item.asp?P65=&tool=power&item_ID=

In cell C1, I pasted the actual part number
87381
Actually, I pasted all 5000+ part numbers in column C but 87381 was in C1

In cell D1, I pasted the last part of the address beginning with the & that
is just after the part number (87381)
&group_ID=898&store=snapon-store&dir=catalog

I then copied B1 and pasted it down the B cells all the way down to my last
part number in column C
Did the same for D1, I copied D1 and pasted it down the D cells all the way
down to my last part number in column C

Now the formula
I made my first formula (function) in A1
I clicked on fx and chose the hyperlink function and pressed OK.
The Function Arguments box pops up.
With the blinking line in the link_location box,
I clicked on B1,
I typed & ( shift 7)
I clicked on C1,
I typed &,
I clicked on D1
It should look like this
Link_location B1&C1&D1

I then clicked on Friendly_name
I picked the part number in D1 for a friendly name

Click OK

In cell A1, you should have 87381.
The formula should look like this
=HYPERLINK(B1&C1&D1,C1)

You can now copy A1 and paste it down the A cells all the way down to your
last part number in column C

Option, I hid column B, C and D after that but that would be for esthetics
only

Hope this helps someone some day

 

[Steve Dunn]

Hi Pierre, you don't need the extra columns:

=hyperlink("http://buy1.snapon.com/catalog/item.asp?P65=&tool=power&item_ID="
& C1 & "&group_ID=898&store=snapon-store&dir=catalog" , C1)

will work fine.