• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Row of Last Edited Cell in range function?

S

Sherwood Botsford

New Member
Is there a way to get the row number of the most recently edited cell in a range? Not a cell that was changed by a formula, but one that the user modifed either through a drop down list or by actual entry?


First attempt:
=CELL("infotype" [Range])

One of the infotypes is 'row'

BUT if I read the docs correctly if Range is present, the function returns the property of the upper left corner of the range, but if Range is not present it returns property of the active cell as of the last recalc.

At present I think I will use infotype of Address, then have to have a cell that figures out if the change is within the correct range.

Is there an IsInRange function?
 
Hi Sherwood ,

=CELL("ROW") does give the row number of the last manually altered cell.

I have tried this with a data validation dropdown , the =NOW() formula and with manual data entry ; pressing F9 to change the time entered through the =NOW() formula does not change the result of the =CELL("ROW") formula.

Changing the DV dropdown selection and any manual data entry in a cell do change the formula result.

Narayan
 
Yes, but I want to limit the target to a range. E.g. which row in C2:E100

=Cell("row",C2:E100) always returns 2 -- the start of the range.

Is there a different function I should be using?
 
Last edited:
Hi Sherwood,

We've had similar discussion here:
http://chandoo.org/forum/threads/using-cell-row-function-to-create-time-stamp.18434/

You may have to resort to some combination formula.
=IF(SUMPRODUCT((ROW(C2:E100)=ROW(INDIRECT(CELL("address"))))*(COLUMN(C2:E100)=COLUMN(INDIRECT(CELL("address"))))),"Last Mod cell is in the specified range and has row : "&CELL("row"),"Last Mod cell is outside specified range!")

I would suggest VBA sheet based event for better control.
 
Hi Sherwood ,

Sorry but I am not able to understand why you wish to limit the output of the CELL function to a range. After you have got the row of the last edited cell , you can always check whether it falls between 2 and 100 ; if it does , you take what ever action you would like to take.

If you can explain what your objective is , it may be possible to suggest the appropriate formula.

Narayan
 
You must log in or register to reply here.
Back
Top