Variable Cell Reference
 

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

Variable Cell Reference
 

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

Variable Cell Reference
 

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

"Don Guillett" wrote:

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