Formula for date to appear on entry of data to cell

[sjm44]

Hello,

I did try and search for a similar topic but couldn't come across anything, although I'm not even sure I was searching for the right keywords so apologies if I have missed anything.

I am using Windows 7 and Excel 2010.

What I am trying to do is create a form in Excel where when I enter data into a specific cell (B2), the date of which it what entered goes into cell (A2).

I am aware that "ctrl ;" does this but as of yet I have had no success adding this into an IF Function or a macro.

Any help would be much appreciated.

Thanks,

Simon.

 

[Weaver]

Welcome to the board.

You'll need to access excel events to achieve this, specifically the worksheet_change event.

Will you be changing 1 line at a time or are multiple updates possible?

If the former, then you need to right click the sheet tab, choose 'view code' and add the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column = 2 Then
            Application.EnableEvents = False
            .Offset(, -1).Value = Now()
            .Offset(, -1).NumberFormat = "dd/mm/yyyy"
            Application.EnableEvents = True
        End If
    End With
End Sub

 

[sjm44]

Welcome to the board.

You'll need to access excel events to achieve this, specifically the worksheet_change event.

Will you be changing 1 line at a time or are multiple updates possible?

If the former, then you need to right click the sheet tab, choose 'view code' and add the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column = 2 Then
            Application.EnableEvents = False
            .Offset(, -1).Value = Now()
            .Offset(, -1).NumberFormat = "dd/mm/yyyy"
            Application.EnableEvents = True
        End If
    End With
End Sub

Just what I am looking for. Thanks a lot.

One more thing if possible. How easy is it to file a record in a row by a company name for example into a separate specified sheet?

If it's too much trouble, don't worry. What you've helped me with so far is a big help.

 

[Weaver]

If it's multiple rows, the usual method is to filter the data for the specific criteria then copy/paste the visible rows. This is something covered a lot on this forum - it's a useful function, so you should be able to find something. Here's one possibility

http://www.mrexcel.com/forum/showthread.php?t=451730&highlight=split+data+criteria

at least I think that's what you're after.