Using activecell in vlookup

[csdjj]

Hi, I want to create a vlookup statement (just in Excel; not via a macro) but
I want the vlookup value to be whatever cell the user has selected (I think
this is defined as the activecell, right?).

So, for instance, if the user has selected cell A3 then my formula would be
vlookup(a3,b1:b10,1,false). But, if the user then selects cell A10 then the
formula would automatically change to vlookup(a10,b1:b10,1,false).

Can that be done?

Thanks!

 

[Luke M]

Yes, but you will need to use a bit of VBA. Also, before beginning, a word of
caution that changing a formula everytime you change selection in a worksheet
could lead to odd results. But here we go:

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

 

[csdjj]

That is FABULOUS! Thanks so much!

Yes, but you will need to use a bit of VBA. Also, before beginning, a word of
caution that changing a formula everytime you change selection in a worksheet
could lead to odd results. But here we go:

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

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*