Hyperlinking

[Niccalo]

Here is the formula I am using

=IF(A1="","",VLOOKUP(A1,B:D,3,FALSE))

Column 3 is a list of names, and each of them is set up as a hyperlink to open to various file locations. Using the above formula I can get the folder name to show, but it removes the formating for the hyperlink location. Is there anyway to fix this?

[mangesh_yadav]

The cells are hyperlinked and not the text in your table. Probably you will have to write a macro to change the link at run-time.

Also, you would need to save the link information in another column for this purpose.

Mangesh

[mangesh_yadav]

Example:

You have the following table in range A1:C3
1 Bk1 C:\Book1.xls
2 Bk2 C:\Book2.xls
3 Bk3 C:\Book3.xls

In A10, you enter a number from 1 to 3
In B10, your formula:
=VLOOKUP(A10,A1:C3,2)

The following code should be entered in the module for the above sheet. This will add the link to you cell B10

Private Sub Worksheet_Change(ByVal Target As Range)
if Target.Address = "$A$10" then
Range("B10").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=WorksheetFunction.VLookup(Range("A10"), Range("A1:C3"), 3)
end if
End Sub


Mangesh