Attaching data to other data

[Rivermark]

I have a list of clients, which changes quite often. Every month I have to do an invoice which includes those clients, some clients have been there for several months and some only several days.

I would like an entry date to be attached to the specific person, so when I create the invoice and add their name, the corresponding entry date column automatically fills in with their date of entry.

I'm sure there is information that you need me to be more specific on, but not sure where to start.

 

[Jerry Sullivan]

Hi Rivermark,

You can do that with worksheet_change event code.

Let's assume you want to enter your client names in Col B and have today's date automatically entered into Col A. So the end result looks something like this...

Excel Workbook
	A	B
1	Entry Date	Client Name
2	7/19/2011	ABC Company
3	7/19/2011	XYZ Inc
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    On Error GoTo CleanUp
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        If Target.Row = 1 Then Exit Sub 'Header row
        Application.EnableEvents = False
        If Len(Target) > 0 Then Target.Offset(0, -1).Value = Date
    End If
CleanUp:
    Application.EnableEvents = True
End Sub