Using CELL("row") function to create time stamp

[shrivallabha]

I was going through some time stamps options shown up on the internet including the one posted by Chandoo. Mostly they seem to use iterations to create time stamp.

In MS Help for CELL function I read that it checks information for the last changed cell if the optional reference is kept empty.

• reference Optional. The cell that you want information about. If omitted, the information specified in the info_type argument is returned for the last cell that was changed. If the reference argument is a range of cells, the CELL function returns the information for only the upper left cell of the range.

Suppose we have table data in A2:F20 and if we put a formula like below in cell G2:
=IF(CELL("row")=ROW(),NOW(),G2)
and copy it down. It does create a time stamp (with circular reference warning) when data is changed in that row. It is volatile!

I am not sure if this is useful or not but there could be some places where it could be used (not just stamps).

Please let me know your thoughts and observations on this.

 

[Luke M]

Cool trick! I can see that you could elaborate on this to look at a specific cell like:
=IF(CELL("address")="$A$2",NOW(),G2)
to update when cell A2 is changed.

Will defnitely keep this little tidbit in mind for future developments.

 

[shrivallabha]

Thanks Luke. I was not sure if it would be good utility so I posted in Excel questions instead of vault.

It can be used used for options specified in the CELL argument e.g.

• Monitor particular cell ("address")
• Monitor particular row ("row")
• Monitor particular column ("col")

There are other options as well which could be used.

As of now I am not sure of its downside except that it is volatile and creates a circular reference warning.

 

[jeffreyweir]

Think I'd always use a macro rather than the circular reference approach. Never introduce a circular reference without a damn good reason, because it will mask any further circular references that you add in error.

That said, it is cool to know that you can do something based on what cell is active.

 

[shrivallabha]

I agree. I have not encountered a situation like above. In such cases, the applications that have databases in the background which track the time, username etc. are better. Or shared workbook (another not recommended) lets you track changes as well.

But if there's blog article dedicated for this then probably it is required somewhere.

The reason I posted this was to see if others had used this option somewhere else.

e.g. sometimes on a sheet heading users want the date/time when records were last updated then it can be deployed in cell A1 like:
="Last updated on : "&TEXT(IF(CELL("address")<>"$A$1",NOW()),"DD/MM/YYYY HH:MM:SS")
Just an idea!

 

[KING JHAMES]

I agree. I have not encountered a situation like above. In such cases, the applications that have databases in the background which track the time, username etc. are better. Or shared workbook (another not recommended) lets you track changes as well.

But if there's blog article dedicated for this then probably it is required somewhere.

The reason I posted this was to see if others had used this option somewhere else.

e.g. sometimes on a sheet heading users want the date/time when records were last updated then it can be deployed in cell A1 like:
="Last updated on : "&TEXT(IF(CELL("address")<>"$A$1",NOW()),"DD/MM/YYYY HH:MM:SS")
Just an idea!

i love this idea of "last update". what if i want it to also display "by username" along with the timestamp, how do u go about it?

 

[shrivallabha]

Sorry, the Cell function does not take username argument.

 

[Syedali]

yes i am also tried,the Cell function does not take username argument.