I am trying to install a timestamp into my workbook in col V. Col U contains the data I would want time stamped. Here is the formula:
=IF(U2<>"",IF(V2="",NOW(),V2),"")
No conditional formatting present
circular formulas have been turned on
I do not get an error I just get nothing. Formula evaluator is not helping either as it is focused on the circular aspect of the formula
Please advise
Is column V going to have information typed into it manually? If not, put this in V2
Please Login or Register to view this content.
1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.
Tom
OK I think I know what is going on here. This page has been extensively coded with VBA and there is a portion that eliminate possibility of altering an entry once it has been entered. That was supposed to just afffect that column though. Anyway, gmr4evr1, I tried your version and it worked however it timestamped v2 all the way to the bottom of the table - v113. WHen I tested it a few minutes later it did the same thing - time stamped every cell of the table in the v column.
The formula I posted works fine on other pages, but this is the main page and has some protection in the VBA.
ctrl ; ctrl: works just fine...maybe I should record a macro
You can't use formulas to calculate time stamps. There are no formulas that calculate one single time, then freeze their values.
The NOW() function is volatile and recalculates whenever any cell in the workbook recalculates. Consequently, any cells containing NOW() would ALL display the same date/time.
You'd need to either manually enter the time stamp
Which you could easily do by:
• Hold down CTRL
• Press ;
• Press SHIFT+;
• Release CTRL
or
Use VBA to recognize the change and enter the time stamp.
Hi opusarlo,
The circular reference is probably causing the problem. My results are similar to yours.
When I edit cell 'V2' (actually making no changes) I get the timestamp in V2.
I suggest you add a helper column:
U same as is
V helper Column (hidden)
W former column V
Formula:
Please Login or Register to view this content.
Lewis
Please note Ron's answer about volatile nature of function now()
Last edited by LJMetzger; 01-06-2016 at 11:39 AM.
A simple recorded macro will add the date and time to the active cell
The line Keyboard Shortcut: Ctrl+t enables Ctrl + t to trigger the macro. You can change this to whatever suites you.Please Login or Register to view this content.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
A little more concise:
Please Login or Register to view this content.
A little more concise:
Please Login or Register to view this content.
you make a good point, Ron. NOW fallibility removes numerous options. At least it makes it easier to choose with many less options. I decided to go with a recorded Macro. I did the ctrl: stuff and set it to hot key alt+b. I really like your VBA, but I am not sure what would trigger it. If it has to be manually triggered then I will just stick with what I have. It is like my truck - not new, but gets the job done.
Last edited by opusarlo; 01-06-2016 at 04:21 PM.
I have enabled circular formulas just for this and am getting now errors. I think it has to do with the fact that I am working off of a data connection and not the original table
I am marking this as solved. It isn't a shiny new penny, but I am getting time stamps at least. Thank you all for your help
Go to the Developer Tab and click on the recorded macro. Click on Options and select the key selection that you want for the macro to run. You are limited to using the Ctrl key either by itself or shifted and a letter.Originally Posted by opusarlo
After you have made that selection, use that Ctrl + letter to trigger the macro and it will fill the date and time into the active cell.
You can delete the line in the macro that has the shortcut key as it is only a bit of text that doesn't get executed.
The shortcut keys to trigger it is set as outlined above.
After playing around a bit with Ron and Lewis' versions of the macro I found that you can select the worksheet tab, View Code and enter the following and then close the editor, go to the Developer Tab, find the macro, click on Options and assign the keyboard shortcut. The macro will be available in that workbook only but it will be available for all worksheets in the workbook.
Please Login or Register to view this content.
If the column 'U' value is changed manually or by VBA, a Worksheet_Change() event can automatically put the value in Column 'V'. If the value in column 'U' is changed by formula, a Worksheet_Calculate() Event may be able to changed the value in column 'V'.
The following code would go in the Sheet Module. If there were more than one Sheet, the following code (slightly modified) would go in the ThisWorkbook code module:
LewisPlease Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks