If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Hyperlink to the meat of a cell
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 |
#2
|
|||
|
|||
Hyperlink to the meat of a cell
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 |
#3
|
|||
|
|||
Hyperlink to the meat of a cell
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 |
#4
|
|||
|
|||
Hyperlink to the meat of a cell
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....re&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....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 -- Pierre "Pierre" wrote: 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 |
#5
|
|||
|
|||
Hyperlink to the meat of a cell
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. "Pierre" wrote in message ... 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....re&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....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 -- Pierre "Pierre" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|