Variable Cell Reference

[Stormy]

Hi,

I'm using a lookup function where the values in cells C2:E2 are returned
from a whichever row of a separate table matches the value of B2 (a very
simple VLOOKUP).

Instead of having B2 as an absolute value, can I enter a formula that will
make B2 equal the value of whatever cell I select? For example, if the value
of A2 is 8 and I select it, then B2 becomes 8, or if the value of A5 is 120
and I select it, then B2 becomes 120?

I may not have explained this very well, but hopefully someone might be able
to help?

Thanks

 

[Don Guillett]

If you are sure that is what you want,
Right click sheet tab>view code>insert this. Now, any single cell selected
will transfer that value to b2

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("b2") = Target
End Sub

 

[Stormy]

That is exactly what I wanted to do - thanks very much!