Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLOOKUP Help
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 |
#2
|
|||
|
|||
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. ---------------------------------- "Chris Manning" wrote in message ... 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 |
#3
|
|||
|
|||
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 "Chris Manning" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
make a vlookup using a variable path | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions |