How to use the rownumber of the current cell in a formula

[Wim Goelen]

Hi,

is there a way to retrieve the rownumber of the cell that "has the focus"? I
would like to calculate a number of values in the top row, but the formulas
need the rownumber to look up the correct prices.

In the formula below, the cell will contain a value of 50, 20 or zero,
depending on where the user's cursor is placed, the rownumber is then put in
D5

=IF(INDIRECT(CONCATENATE("H";D5))>0;50;IF(INDIRECT(CONCATENATE("L";D5))>0;20;0))

Thanks,

Wim Goelen

 

[Wigi]

You could use the function ROW() (so without argument between brackets).

 

[Sheeloo]

Only through VBA - ActiveCell.Row

 

[Michael]

After you hit Alt F11, when you see the vba Project window on the left hand
side of your screen click on the Sheet Name that you want to add the code on
For example Sheet1 and the right hand side select from the first dropdown box
Worksheet and from the second dropdown box choose selection change and paste
the code below:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MyRow = Target.Row
Range("A20").Value = MyRow
End Sub
Then on your formula point to A20 to provide the active row number.