Need today's date to auto fill in a cell when data is entered into another cell

I would like to have cells C5-C39 auto fill today's date whenever data is entered into cells B5-B39.

I have tried to get it to work using =TODAY() but don't how to structure the formula.

4 Answers

I'd say that the simplest way to do this would be with a user-defined function. That function will update when any cell it references change, so you won't have to tie it into any events.

A function for your problem would be:

Function Updating_Date(dependent_cell as Range) As Date Updating_Date = Date
End Function

After having put that code into a module in your workbook, you can simply input =Updating_Date(B5) into C5 and then copy it down. That should make the date in C5 update whenever the values in column C changes.

In order to make the date only appear if the cell in column B isn't blank, you could apply an if-formula in the cell in column C, as I touched on in the other question you asked. Simply enter the same conditional as I showed there (i.e. ISBLANK(B5)) and have the return-value be an empty string ("") if the condition is true, and the UDF (Updating_Date(B5)) if the condition is false.

The following puts today's date in cell "B" whenever there is a change in cell "A":

Private Sub Worksheet_Change(ByVal Target As Range)
' Auto Date
Dim Cell As Range
For Each Cell In Target If Cell.Column = Range("A:A").Column Then If Cell.Value <> "" Then Cells(Cell.Row, "B").Value = Int(Now) Else Cells(Cell.Row, "B").Value = "" End If End If
Next Cell
End Sub

This code needs to be placed in the VBA module for the worksheet where you want this behavior to occur.

You can do this without VBA. I needed my C column to fill in with today's date based on the B column have a value entered. This formula works to get it inserted correctly.

=IF(ISBLANK(B2)," ",NOW())

I placed this in the C2 cell since that is where I was starting for my C column. You can place this in whichever column's cell you're starting in. Then just make sure that my B2 reference is replaced with the column and cell your value is being entered into. For example, if your kick-off cell is F3, you should change the formula to say

=IF(ISBLANK(F3), " ",NOW()). 

And if your corresponding date cell is K3, place formula there. (You can drag the formula to the rest of the column's cells once it's in the first one.)

This formula keeps the date cells empty until the "kick-off" cell has a value entered. Once that value is entered, the date is added. If the value is deleted, Excel will automatically remove the date. So it's not perfect, but if you need something more sophisticated, you probably need an Excel expert or a program that's handling this for you.

AFIK, you will need to do this via VBA. You can create a function that will watch for changes on your worksheet, filters out only changes on cells B5-B39 and updates the cells in C accordingly.

UPDATE: You will probably know to get into VBA using alt-F11.

Double click on the Excel object called Sheet1 or whatever sheet you are interested in (in the tree list top-left) to get to the code for that sheet.

Select Worksheet and Change from the 2 dropdowns. So you get code that looks like:

Private Sub Worksheet_Change(ByVal Target As Range)
End Sub

The variable "Target" is now available which is a reference to the cell that changes.

Add some code between those lines:

debug.Print Target

and hit ctrl-G to show the immediate window

Make some changes in the worksheet and switch back to the code, you will see the data you entered appear in the Immediate window.

Now change the code to say if Target. and you will see a drop-down of possible properties that relate to the Target variable.

With a couple of changes, you can get this:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 2 And (Target.Row > 4 And Target.Row < 40) Then Debug.Print Target.Value End If
End Sub

Which now only prints changes in the range you are interested in.

If you want to know how to change a value in a cell, you can record a macro of you doing that manually and examine the code. Hopefully you have enough to go on now. Let me know if not.