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

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" wrote in
message ...

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

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.


--
hwest13
------------------------------------------------------------------------
hwest13's Profile: http://www.excelforum.com/member.php...o&userid=23995
View this thread: http://www.excelforum.com/showthread...hreadid=376171