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....re&dir=catalog
7585
http://buy1.snapon.com/catalog/item....re&dir=catalog
7641
http://buy1.snapon.com/catalog/item....re&dir=catalog
I really hate to have to admit this but for your second suggestion, you are
way over my head
--
Pierre
"JLatham" wrote:
The formula you want would be:
="http://buy1.snapon.com/catalog/item.asp?P65=&tool=power&item_ID=87381&group_ID=89 8&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.asp?P65=&tool=power&item_ID=87381&group_ID=89 8&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" wrote:
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....re&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
--
Pierre