How to concatenate a url and the value of another cell

[hwest13]

Hello,

What I'm wanting to do is to have a column of cells formatted as hyperlinks that I can just click on and have them take me to a website (the website will be the same for all of them). That part I can do. However, I want the cell's url to pull in a value from another column and add this value onto the end of the website url. To be more specific, what I have is a logfile with usernames and ip addresses. I then have a third column of cells that I want to contain the url and I want each of these url cells to append the ip address that occurs in that record to the end of the url and I want to be able to simply click on this third cell and be taken to a website that will give me information about the source of the ip address.

Here is a sample of the format of a record:

Name IP Address Website

John Doe 66.66.66.66 http://www.ipchecker.com/search?Searchstring=


I want to use a variable to append the 66.66.66.66 to the end of the url, right after the "=" sign. I tried doing it this way: I formatted the website cell as a URL with the above information in it, but I then changed it to look like the following:
="http://www.ipchecker.com/search?Searchstring="&B2
where B2 was the cell that contained the IP address. This does allow the cell to look correct (i.e. the cell then displays http://www.ipchecker.com/search?Searchstring=66.66.66.66). However, when I click on the cell, it merely takes me to http://www.ipchecker.com/search?Searchstring= and it does not actually append anything to the url on the web page itself. Any ideas on how to fix this issue?

Just FYI, what I'm working with is a firewall log containing thousands of these types of records, so I really do need to use variables.

Thanks for any ideas!

[Bob Phillips]

Here is one way, but it requires VBA.

Firstly, assuming that the URL is in column C, that column C contains the
full URL, and column B the IP address,
change all of the hyperlinks in the column C to point to somewhere in the
worksheet, say the same cell
then add this code

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target
As Hyperlink)
ThisWorkbook.FollowHyperlink Address:=Target.Name &
Range(Target.Range.Address).Offset(0, -1), NewWindow:=True
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob Phillips

"hwest13" <[email protected]> wrote in
message news:[email protected]...
>
> Hello,
>
> What I'm wanting to do is to have a column of cells formatted as
> hyperlinks that I can just click on and have them take me to a website
> (the website will be the same for all of them). That part I can do.
> However, I want the cell's url to pull in a value from another column
> and add this value onto the end of the website url. To be more
> specific, what I have is a logfile with usernames and ip addresses. I
> then have a third column of cells that I want to contain the url and I
> want each of these url cells to append the ip address that occurs in
> that record to the end of the url and I want to be able to simply click
> on this third cell and be taken to a website that will give me
> information about the source of the ip address.
>
> Here is a sample of the format of a record:
>
> Name IP Address Website
>
> John Doe 66.66.66.66 http://www.ipchecker.com/search?Searchstring=
>
>
> I want to use a variable to append the 66.66.66.66 to the end of the
> url, right after the "=" sign. I tried doing it this way: I formatted
> the website cell as a URL with the above information in it, but I then
> changed it to look like the following:
> ="http://www.ipchecker.com/search?Searchstring="&B2
> where B2 was the cell that contained the IP address. This does allow
> the cell to look correct (i.e. the cell then displays
> http://www.ipchecker.com/search?Sear...ng=66.66.66.66). However,
> when I click on the cell, it merely takes me to
> http://www.ipchecker.com/search?Searchstring= and it does not actually
> append anything to the url on the web page itself. Any ideas on how to
> fix this issue?
>
> Just FYI, what I'm working with is a firewall log containing thousands
> of these types of records, so I really do need to use variables.
>
> Thanks for any ideas!
>
>
> --
> hwest13
> ------------------------------------------------------------------------
> hwest13's Profile:
http://www.excelforum.com/member.php...o&userid=23995
> View this thread: http://www.excelforum.com/showthread...hreadid=376171
>

[hwest13]

Thanks for the reply, Bob! Later, last night, I discovered what I needed.
The following method worked for me perfectly:

=HYPERLINK(CONCATENATE("http://www.ipchecker.com/search?searchString=",B2))

Then, I just drag this formula down and it populates all the rest of the rows with the correct information. I'll have to try the method you posted, as well. For whatever reason, the B2 portion of the cell was not getting treated as part of the URL before I ran across the concatenation method shown above.

Thanks again, Bob.