Date Stamp based on cell value

Good Evening Everyone,

First I want to thank everyone for all the great information that is here.
Now my problem, I have a worksheet that is a status report for jobs we have, there is one column that gets a number code.
What I am trying to do is have one of the next 6 cells get a date stamp based on the value in column in A.
So if A = 1, I would like a date stamp in column B
If A = 2, I would like a date stamp in column C,
If A = 3, I would like a date stamp in column D, etc.
But it should not change any of the previous date stamps.
The next part is that I want to be able to use that date stamp like:
3 would stand for "completed" so in a table I could use VLOOKUP so:
A1&" "&D1 (Completed 8/7/12)

Thanks

Hi there,

I have added a worksheet_change macro to the sheet1 in vb, the code determines the last row within sheet1, then loops through the rows 2 to 100, for example, determines the stage inputted in column A and date stamps the appropriate column.

Hope this helps.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If

For n = 2 To LastRow

If Target.Address = ActiveSheet.Cells(n, 1).Address And ActiveSheet.Cells(n, 1).Value <> 0 Then
x = ActiveSheet.Cells(n, 1).Value + 1
ActiveSheet.Cells(n, x) = Format(Now(), "dd/mm/yyyy")
End If
Next n
End Sub

Test File.xlsm

Does this help at all?

Thanks Tayque,

The first parts works great, the second part where I want to be able to use that date stamp like:
Code 3 would stand for "completed" so in a table I could use VLOOKUP so in a different cell I could have:
A1&" "&D1 (Completed 8/7/12)

Thanks

If I'm understnading you correctly

Column a = 3, column b = previous date, column c = previous date, and column d = Completed and Date

If I'm understnading you correctly

Column a = 3, column b = previous date, column c = previous date, and column d = Completed and Date

Actually, I guess I'll complicate matters more.
I thought there might have been a table or reference created that I could change/modify as needed. I looked at how you did the datestamp and I don't see any table, just that the input if incremented by 1 will move to the next column.
I used "code" 1, 2, 3 as an example. The real "code" numbers that would be input in column M are more like 11,21,22,65,9,19,6,16,45,0 (Usually in this order)
For example:
M30=11, than the date stamp would go in T30
M30=21, than the date stamp would go in U30
M30=19, than the date stamp would go in Y30
This information is saved as a reference when someone wants this info on a job.
Column P, we are manually putting in info like "PACKED 8/8/12", so I wanted to make it so that if:
M30=45, than P30="PACKED 8/8/12".
I have a reference table that I can VLOOKUP that 45=PACKED but when I M30&" "&AB, I get PACKED 41129.
Ok, I just also realized that Column P would have to VLOOKUP the "code" and pull the date stamp from the corresponding column.
Oh well
Let's see what we come up with

Thanks

hi there, would you be able to provide the excel file, with any sensitive data remove and we can look at what we can do

Thanks Tayque,
I attached a simple mock up of the database with comments showing what I am trying to do.
The date stamps are used in a userform when someone searches for that info.
I guess once I get a copy of the columns to work properly, I can figure out the rest.

Thanks again