Vlookup based on Cell selection

[morleym]

Hi all,

Apologies if this has already been covered, but I couldn't find anything with a search...

I have a table, 1 column wide (col A), 20 odd rows deep with various descriptions. I'd like to be able to click/select any one of those cells (A1-A20) and have a more detailed description pop up in a merged cell, C1:D4 - which is based on a lookup of a table that I can create further down the sheet (say A100:B120).

Is this possible?

Many thanks in advance!
Marc

 

[morleym]

Just found my answer elsewhere. My thanks to "Luke M" for his post on www.pcreview.co.uk

In your VLOOKUP, replace the cell reference with a name, such as MyRange
Example:
=VLOOKUP(MyRange,A:C,2,FALSE)

Now, right click on the sheet tab, go to view code. Paste this in:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveWorkbook.Names.Add Name:="MyRange", RefersToR1C1:=ActiveCell
End Sub