Stop now function auto updating when new row added

**mountainfarm** · 10-29-2008, 04:24 PM

I have fought with this for 2 days and I am trying to have a worksheet cell update the date and time if the cell it is referencing (B3 see below)

Here is the formula I am using however it updates every time a new row is added to the worksheet. So the Date/Time stamp on each row is always updated to NOW() everytime.

=IF(ISNA(B3),"0/0/00 00:00",NOW())

I would like to have a Date/Time stamp stay as is once that row has been added. Is there anyway to stop the field from updating once the date and time have been set?

ARGGHHH!!

**VBA Noob** · 10-29-2008, 04:37 PM

Maybe this event macro.

Right click sheet tab > select view code > paste in the below. Change DestCell address to the cell reference you want the date and time to appear

Please Login or Register  to view this content.

HTH

VBA Noob

**mountainfarm** · 10-29-2008, 10:32 PM

I should have added, each row of the sheet contains a Date/Time reference and when you enter in the information in the first cell it utilizes that data to do a VLOOKUP in the B column range, the C column range is the Date/Time reference that I want date/time stamped a single time and not changed...

I believe your solution will only work for a single cell reference. If I am wrong just #)$(% yell back at me to straighten me out. I appreciate your help with this.

**VBA Noob** · 10-30-2008, 03:23 AM

You are correct. So you could try

Please Login or Register  to view this content.

VBA Noob

**mountainfarm** · 10-30-2008, 06:25 PM

This does the opposite. It initially fills all fields with NOW (date and time) and then when information is added to a row it turns all cells in the C column to "00/00/00 00:00"

I tried flip flopping the NOW and "00/00/00 00:00" in the code and it in turn changes all rows in the worksheet to the current NOW() when any new information is added to the A column in a row.

Here is a copy of the four cells across each row with the formulas shown, maybe this will make it easier to understand

125000000000 =VLOOKUP(A8,upcdb!$A$2:$B$2197,2,FALSE) 39751.7661226852 =IF(ISODD(COUNTIF(A3:A200,A8)),"IN BUILDING","OUTSIDE")
(blank cell) =VLOOKUP(A9,upcdb!$A$2:$B$2197,2,FALSE) 0/0/00 00:00 =IF(ISODD(COUNTIF(A3:A200,A9)),"IN BUILDING","OUTSIDE")

Here is what the data looks like

125000000000 Abell, Dave 10/30/08 6:23 PM IN BUILDING
(Blank cell) #N/A 0/0/00 00:00 OUTSIDE

**VBA Noob** · 10-31-2008, 03:14 AM

See if this helps otherwise post a sample file

Please Login or Register  to view this content.

VBA Noob

**royUK** · 10-31-2008, 04:37 AM

The NOW Function is vlatile, ie it will update. You can create a static timestamp using a User Defined Function. Insert a module in the workbook and copy this code

Please Login or Register  to view this content.

In any cell test it by typing the formula =StaticDate(). Format the cells to show date & time eg dd/mm/yyyy hh:mm.

Your formula would be

=IF(ISNA(B3),"0/0/00 00:00",StaticTime())