Date-time updates every cell?
I have a worksheet that has a formula in all cells in column H that says if there is a comment in column G then put the current date/time in H(current cell). My formula looks like the following in H5; =IF(ISBLANK(G5),"",NOW()) The problem is that if I enter a comment in any cell in column G it updates all of my dates in column H. Using Excel 2007 |
Date-time updates every cell?
The NOW() and TODAY() functions are meant for you to have formulas take action action the current date (and/or date-time), not as a time stamp. You can do what you want using VBA event code. Delete all of your formulas in Column H, right click the tab at the bottom of your worksheet, select View Code from the popup menu that appears and copy/paste the following code into the code window that opened up... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 7 Then Application.EnableEvents = False If Target.Value < "" Then Target.Offset(, 1).Value = Now Else Target.Offset(, 1).Value = "" End If Application.EnableEvents = True End If End Sub Now, when you go back to your worksheet and enter something in Column G, the date and time will be placed next to it in Column H. As written, if you delete an entry in Column G, the date that was originally displayed for the comment in Column H will be deleted as well. -- Rick (MVP - Excel) "Selah West" wrote in message ... I have a worksheet that has a formula in all cells in column H that says if there is a comment in column G then put the current date/time in H(current cell). My formula looks like the following in H5; =IF(ISBLANK(G5),"",NOW()) The problem is that if I enter a comment in any cell in column G it updates all of my dates in column H. Using Excel 2007 |
All times are GMT +1. The time now is 11:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com