IF Forumula carrying down entire column

[chrisduke]

Hi all,

Joined the forum recently and have been lurking around trying to learn some new things.

I am completing a data tracker for work, and one of the things I need it to do is automatically update the date in a cell once data is entered in the cell to the left of it.

What is happening is that it is updating the entire column, not just the one cell next to where I have entered data. I am using this formula: =IF(K3<>"",NOW()), and for each cell it changes accordingly, i.e. =IF(K4<>"",NOW()), =IF(K5<>"",NOW()) etc...

Even if I only change or enter data in K3, it is changing the date and time all the way down the column...what's wrong?

Chris

[Spencer101]

=NOW() is volatile. That means every time there is a change to the worksheet it will update.
Try it by just tapping F9 a few times to refresh the page.

There are ways around this but they usually involve a certain amount of VBA.

[Pepe Le Mokko]

If you only need the date use the TODAY() function ( also volatile)

[Tony Valko]

See this...

http://www.mcgimpsey.com/excel/timestamp.html

[chrisduke]

If you only need the date use the TODAY() function ( also volatile)

I tried this as well before posting - same issue

[xwarlock10x]

in cell D5 put
=IF(k3<>"",IF(D5="",NOW(),D5),"")
the D5 is the input cell where the data will return.. this is a circular reference.. so youll have to go to the excell button> excell options> formulas> under calculation options check mark the box that says enable iterative calculation and then change the maximum iterations from 100 to 1.. in the colmun where the time stamp is format to time.. that's it.. it will hard code the time if anything is typed in the K column..

[Tony Valko]

That technique is described in the link provided in post #4 (if anyone took the time to look!).

If you set iteration first then enter the formula you won't get any popup warning messages from Excel.

[chrisduke]

Hi,

I tried both formulas in posts 4 and 5, but both options are displaying the date and time as "1/0/00 0:00"

The VB option doesn't work for me only because the options are different in 2010...

[chrisduke]

I now entered the following VB code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Offset(0, 1).Value = "" Then
Target.Offset(0, 1) = Format(Now(), "HH:MM:SS")
End If
End Sub

The time now changes independently in each cell, but the date says "1/0/00"

[Spencer101]

Any chance of an example workbook?

[Jonmo1]

Do you want the Date or the TIME in the cell? Or both?

Your code is only putting the TIME in the cell
Target.Offset(0, 1) = Format(Now(), "HH:MM:SS")

If you want the DATE in the cell, just change that to
Target.Offset(0, 1) = Date

And format the column as Date.

[chrisduke]

Thanks. I figured that part out earlier, now fighting with getting this code over three columns to work properly.

[chrisduke]

Any chance of an example workbook?

Another thing I will need to figure out...the file is now 35MB and it doesn't even contain all the data it will once it's in use...

[xwarlock10x]

check this one out..

this should work for you

[chrisduke]

check this one out..

this should work for you

I entered it into cell and deleted the vb code, displays as 1/0/00, 0:00

[Pepe Le Mokko]

Did you enable iterative calculations in the Options - Formula ? ( it's under the Files tab in 2010) and format the cell to "Date" ?

[xwarlock10x]

can you upload a small portion of your sheet..??
your formula should look like this

=IF(k4<>"",IF(B2="",NOW(),B2),"")... the B2 cells in my formula is where you want the data to return to .. so in this instance this formula is in cell B2..so if your time stamp is in cell K5 or K6.. just change the B2's to K5.. etc.. it's a circular refrence but it works fine.. I use this formula to track entries in a Membership only night club and it hard codes time stamp without updating..

[Tony Valko]

now fighting with getting this code over three columns to work properly.

Does that mean the date/time stamp should be 3 cells to the right of the target cell?

If so, you just need to change each instance of:

Target.Offset(0, 1)

To:

Target.Offset(0, 3)

The syntax is:

Offset(rows, columns)

[chrisduke]

Did you enable iterative calculations in the Options - Formula ? ( it's under the Files tab in 2010) and format the cell to "Date" ?

Yes I have it checked off and max iternations are at 1, max change at .001

[chrisduke]

Sorry I don't see a way to attach files....perhaps because I'm a new user?

[xwarlock10x]

on the bottom right corner of the reply .. there is a "go advanced".. click on it. scroll down then youll see a manage attachments.. click on it and follow the directions..

[chrisduke]

ok here is s portion of the sheet

[xwarlock10x]

you did it right.. but the formatting was wrong. i cleared the cells in the "K" column.. and boxed in the cells that have the formula..
now when you type in the K column a time stamp will appear..

here you go.

[chrisduke]

you did it right.. but the formatting was wrong. i cleared the cells in the "K" column.. and boxed in the cells that have the formula..
now when you type in the K column a time stamp will appear..

here you go.

Thanks for fixing it, but what did I do wrong so I know for the future...

[xwarlock10x]

as far as i can see you didnt do anything wrong.. what it looks like you did was enter the formula and the you copied and pasted the info into the K column this giving you the same data all the way down.. your formatting is "date" instead of "Time".. i just fixed your formatting and then deleted the info in the K column.. it was already working..

[chrisduke]

as far as i can see you didnt do anything wrong.. what it looks like you did was enter the formula and the you copied and pasted the info into the K column this giving you the same data all the way down.. your formatting is "date" instead of "Time".. i just fixed your formatting and then deleted the info in the K column.. it was already working..

Thanks for the assistance everyone

[chrisduke]

I trashed the VB code as it was giving me headaches, and rebuilt the sheet from scratch with xwarlockx10's formula. It's working properly now.

Next dilemma is this. There are three different columns with pull downs containing status codes for a project I am working on. The dates will now automatically generate each time I select a status code in the adjacent cell, thanks to xwarlockx10's assistance However what I now need to figure out is how to be able to select multiple status codes per line and the date the status code was selected. I want to do this without making multiple rows or columns for each line. Each line represents a client, and there are hundreds of them per tab (I work for a large company). Any ideas?

Thank you

[chrisduke]

Got everything working the way I want finally...

Thanks all for the help