Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use the rownumber of the current cell in a formula
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use the rownumber of the current cell in a formula
You could use the function ROW() (so without argument between brackets).
-- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Wim Goelen" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use the rownumber of the current cell in a formula
Only through VBA - ActiveCell.Row
"Wim Goelen" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use the rownumber of the current cell in a formula
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. -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Wim Goelen" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CURRENT TIME IN DESTINATION CELL IF SOURCE CELL IS TEXT | Excel Discussion (Misc queries) | |||
formula to change current cell value | Excel Worksheet Functions | |||
formula won't paste from one cell to another with current cell inf | Excel Worksheet Functions | |||
formula, move to previous cell when the current cell=0 or empty | Excel Discussion (Misc queries) | |||
Can I get current #row or #column in cell formula? | Excel Discussion (Misc queries) |