VLOOKUP Help

[Guest]

What I'm trying to do is have the VLOOKUP function return the actual formula
in the reference cell (Specifically, it is a HYPERLINK command) and what
happens is that the VLOOKUP function returns only the "freindly name" of the
hyperlink; that is, the link location is another place in the workbook, but
it returns what is displayed in the hyperlink cell. Is there any way to do
this, perhaps with another function, or modifying the VLOOKUP command? I did
see one solution that was posted on 5/31 involving adding code to the module,
but my problem with that solution is that there are MANY cells that reference
the same table, so that solution is out (it would involve adding near 4000
lines of code to the module. Any help is appreciated.

Chris

 

[Mangesh Yadav]

Hi Chris,

I now know about which thread you were talking. No could you tell me
excactly what you are looking for. Try to give me an example. For instance
lets say you have only 2 cases instead of "A-IS" number of cases.

Mangesh


----------------------------------
This is in regards to a response you posted in the Discussion Group on
the Microsoft website two days ago. You gave her code in order to use
the VLOOKUP function in conjunction with module coding in order to
hyperlink a cell based upon a value entered in another cell. I am
trying to do the same exact thing, but the problem is that I need to
reference the same three column table for MANY cells in the same row
(for reference, my A10 cell and B10 cell are (A-IS)2 and (A-IS)3,
respectively) is there anyway to generalize your code so that each
pair acts independantly, but references the same table? Or am I going
to have to write that if statement for every pair? Any help is
appreciated greatly.

 

[Mangesh Yadav]

Hi Chris,

Here's the solution you are looiking for:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 10 Then
Cells(11, Target.Column).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection,
Address:=WorksheetFunction.VLookup(Cells(10, Target.Column), Range("A1:C3"),
3)
Cells(10, Target.Column).Select
End If
End Sub


Note:
Line 2: change 10 to 2 as your target row is A-IS2
Line 3: change 11 to 3 as that is the next row A-IS3
Line 4 & 5: change 10 to 2

The above code is for my example which was as per the link:
http://excelforum.com/showthread.php?t=375191


Mangesh