Forum Statistics
- Forum Members:
- Total Threads:
- Total Posts: 4
There are 1 users currently browsing forums.
|
 |
06-26-2009, 02:46 AM
|
 |
Valued Forum Contributor
|
|
Join Date: 17 Feb 2009
Location: Littleborough, UK
MS Office Version:Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
Posts: 457
|
|
|
Time Stamping a Cell Entry
Please Register to Remove these Ads
I just saw this article, and learned a new trick! (see link at bottom of page for full article)
The sheet produces a timestamp for a cell, using a formula similar to the one below :--
=IF(C3="","",IF(C3="",D3,NOW()))
Where c3 is the cell to timestamp and d3 is the cell containing this formula!
for this to work Iterative calculation has to be turned on!
But it will record the last change of the cell to the left and not change on re-calculation, only on re-entery!
http://chandoo.org/wp/2009/06/25/tod...racking-tools/
__________________
 No longer looking for work (still if you want to pay me lots of money lets talk!!!)
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare............
Now, thanks to the Internet, we know this is not true.
Robert Wilenski (since there is some confusion I am not Robert he wrote the quote!)
|
06-26-2009, 03:08 AM
|
 |
Forum Guru
|
|
Join Date: 22 Oct 2008
Location: Suffolk, UK
MS Office Version:2002 & 2007
Posts: 13,643
|
|
|
Re: Time Stamping a Cell Entry
Hi squiggler, yes this is a well known trick and is very useful for people on Mac 2008 (and anyone else unable to support VBA), however, I think the references in the example are slightly awry, ie the formula resides in D3 and the entry to invoke the stamp is made in C3:
D3: =IF(C3="","",IF(D3="",NOW(),D3))
The only drawback of this approach when compared to say a classic VBA change event is that it is not updated with changes unless the source (C3) is first cleared, (ie a -> b -> c won't update the time stamp whereas a -> blank -> b -> blank -> c would do).
The first formula listed:
D3: =IF(C3="","",IF(C3="",D3,NOW()))
will if C3 is not blank continuously update the datetime stamp in D3 every time XL recalculates.
|
06-26-2009, 03:12 AM
|
|
Valued Forum Contributor
|
|
Join Date: 17 Feb 2009
Location: Littleborough, UK
MS Office Version:Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
Posts: 457
|
|
|
Re: Time Stamping a Cell Entry
Yes you are correct, I mistyped the formula!
__________________
No longer looking for work (still if you want to pay me lots of money lets talk!!!)
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare............
Now, thanks to the Internet, we know this is not true.
Robert Wilenski (since there is some confusion I am not Robert he wrote the quote!)
Last edited by squiggler47; 06-26-2009 at 03:15 AM.
|
06-26-2009, 04:08 AM
|
|
Valued Forum Contributor
|
|
Join Date: 17 Feb 2009
Location: Littleborough, UK
MS Office Version:Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
Posts: 457
|
|
|
Re: Time Stamping a Cell Entry
Lol, I shouldnt learn new techniques
Still I tried a few things, for a sheet I did, which it would be nice to track cell changes!
Here is the result!
__________________
No longer looking for work (still if you want to pay me lots of money lets talk!!!)
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare............
Now, thanks to the Internet, we know this is not true.
Robert Wilenski (since there is some confusion I am not Robert he wrote the quote!)
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
