Formula to dynamically use content at cursor position

[MSteel]

Hi I can't find the answer in the archive.

I want to dynamically (ie without refresh/F9) use the value at the cursor position in a formula.

so assume I've a list of values in A1....A5

5
10
15
20

and as I move the cursor down the list I want a formula in another cell to use that (for example =n x 100) where n is the value in the list.

I can see CELL("address") can give me location (like $A$1) but how do I do this dynamically ?

 

[Saurabhj]

Hi,

Welcome to Mr.Excel. Kindly use event handler (SelectionChange for Worksheet).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Sheets("Sheet1").Range("D2") = Target.Value * 2
End Sub