Update current dates in different cells when value in a Target column cells changes

[sumit dey]

Hello Everyone,

I am back here with another problem I am facing with a tracker.

I have a workbook wherein there are multiple status in Column N (Data validation list) and I would like to get current date in different cells whenever the status in Column N (range "N2:N1000") changes.

If a cell value in Target Column “N” changes current dates should get updated in the respective cells under different headings (column “V” to “AC”, “AG to “AJ”, Column “AO” to “AU” and Column “AZ” and “BA”.

E.g
If Cell N2= “Status 1” then current date should get updated in Cell “V2”, If Cell N2=”Status 2” then cell W2 should be updated with current date, If Cell N2=”Status 3” then cell X2 should be updated with current date and so on…

Please check the attached workbook wherein there’s a Status column (in Red), when the value in any cell (N2:N1000) in this column changes current date gets updated in the corresponding cells under the columns “V” to “AC”, “AG to “AJ”, Column “AO” to “AU” and Column “AZ” and “BA” (headers in Red font) not in sequence.

I can use the "IF" formula but, Each time I open the workbook the current dates will get updated automatically which is not the purpose I would like the current date to remain unchanged once updated.

[Keebellah]

You should probably do this using a macro to replace the value with the actual date value and not TODAY() which will change every time you open the file
Excel 2003 or so?

[Keebellah]

I tried something

[sumit dey]

Hi Keebellah,

Thanks for the help.

I have checked the updated file you sent but, found that whenever I change status 1 to status 2 the previous date updated according to Status 1 vanishes and the new date gets updated in the new cell under Status 2.
But, I would like the date in the prior cell to remain sticked and unchanged and each time another status changes the new dates should appear under respective status header.

Hope you understood what I just replied.

Thanks again

[Keebellah]

I understand, like I said, it'll have to be done with macro's
Will try some thing and let you know, you haven't answered which version of Excel, .xls goes up to 2003 of newer in compatible mode, but if you're using 2007 or newer you don't need to use xls

[Keebellah]

This one does what you need using a macro
Hope is works

[sumit dey]

Hi Keebellah,

Thanks a ton....it is working exactly the way I was expecting..
I am using Excel 2010 which uses the type xlsm.

I have used the below code which is also working but, yours seems to be easier and lighter and also made me learn a new way to do the same thing.


<Code>
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("N2:N1000")) Is Nothing Then
Select Case Target
Case Is = "In Process"
Target.Offset(0, 1) = Date
Case Is = "Complete"
Target.Offset(0, 2) = Date
Case Is = "status 1"
Target.Offset(0, 3) = Date
Case Is = "Status 2"
Target.Offset(0, 4) = Date
Case Is = "Status 3"
Target.Offset(0, 5) = Date
Case Is = "Status 4"
Target.Offset(0, 6) = Date
Case Is = "Status 5"
Target.Offset(0, 7) = Date
Case Is = "Status 6"
Target.Offset(0, 8) = Date
Case Is = "Status 7"
Target.Offset(0, 9) = Date
Case Is = "Status 8"
Target.Offset(0, 10) = Date
End Select
Target.EntireColumn.AutoFit
End If
End Sub
<Code/>

One More Question.....Say I have selected Status 1 and the current date got
updated under respective column with todays date (15 oct 2015), what will happen if
somebody selects the Status 1 again after somedays intentionally or unintentional
Will the date again get updated with the latest date....
Is there a way wherein the date once inserted won't change with status change (dropdown list) but, can be edit by the user manually or by using any password.

Thanks a lot again

[Keebellah]

Hi, glad it has helped you,
First this, the Status value you select MUST exist in the header row for it to work, so if you add a new status let's say Status 22 you will have to add it in the headers and if the range is outsie the set range you will have to edit the vba code
If you decide to change Status 2 to lets say Repaired, then you must also change the Satus 1 value in the header roe to Repaired, etc.

Now for the last question: you could add a message box verifying if the change is intentional. I would add two tests, if previous selection was blank just execute if the date already present is the same who cares but if the date is older then ask the question.

I hope this makes sense.
If you need help just let me know.

[Keebellah]

I I modified the code for you, added comments to explian it and saved it as Excel 2010 file, much smaller and it works.
Test it and let me know. I also made the column range dynamic in case you decide to add more Status values, you will have to change the code Intersect if the list is longer than row N1001

[sumit dey]

I I modified the code for you, added comments to explian it and saved it as Excel 2010 file, much smaller and it works.
Test it and let me know. I also made the column range dynamic in case you decide to add more Status values, you will have to change the code Intersect if the list is longer than row N1001

Thanks Keebellah you're so much helpful...I hope you keep helping people like this forever....

[Keebellah]

You're welcome, I'll try