If you are sure that is what you want,
Right click sheet tabview codeinsert 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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Stormy" wrote in message
...
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