Project Time Tracker at Each Step

[coolguykets1]

I have below column hedaers from BK11 to BZ11. These are basically steps in my projects.
I have a drop down in AW11 which i basically update once the status changes of a particluar project. I have 10000+ such project with each row as 1 project.
I want to basically track dates for each step. So if i choose today 1st Header data in AW11=OE Samples Procurement then it should populate today date for that row in OE Sample Procurement. If in 2 days i choose next steps as OE Sample Procure. Eval in progress which is second step then it should keep todays date in previous step and add date at that day when i chose the nxt step to that step. If i jump to directlly 3rd step then it should automatically type current dates for all the 3 steps.


[TABLE="width: 1472"]
<tbody>[TR]
[TD]OE Sample Procurement[/TD]
[TD]OE Sample Producred. Eval in progress[/TD]
[TD]Engg ok for PF to Quote[/TD]
[TD]Quote Received [/TD]
[TD]PM Review and Quote Accepted. PF to get Tech Package[/TD]
[TD]Technical Pacakge Received. Engg Release in Progress[/TD]
[TD]Engg Release Completed. For OMS Setup[/TD]
[TD]Soms Setup/costing Completed[/TD]
[TD]EDC - Date Orders Placed[/TD]
[TD]Final PPAP Submission[/TD]
[TD]PPAP Approved[/TD]
[TD]Orders Due[/TD]
[TD]EDC Received Initial Stocking[/TD]
[TD]CM - Part Made Saleable in Soms[/TD]
[TD]Pricing[/TD]
[TD]Available in Catalog[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

If your dropdown is in AW11, how can we tell which row to update?

 

[coolguykets1]

My bad. AW11 is typo in earlier message.
AW12 is dropdown for row 12.
AW13 would be dropdown for row 13 and so on andd so forth.
each row starting row 12 is basically a project and the headers would be in row 11 starting BK11 to BZ11

 

[Fluff]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Fnd As Range

   If Target.CountLarge > 1 Then Exit Sub
   If Not Target.Column = 49 Then Exit Sub
Application.EnableEvents = False
   Set Fnd = Range("BK11:BZ11").Find(Target.Value, , , , , , , , False)
   If Not Fnd Is Nothing Then
      If Fnd.Column = 63 Then
         Target.Offset(, 14) = Date
      Else
         Range("BK" & Target.Row).Resize(, Fnd.Column - 62).SpecialCells(xlBlanks).Value = Date
      End If
   End If
Application.EnableEvents = True


End Sub

It needs to go in the relevant sheet module.

 

[coolguykets1]

Works like a charm. Thanks so Much

 

[Fluff]

Glad to help & thanks for the feedback

 

[coolguykets1]

Another question. Irrelevant to this topic.
I have specific numeric values in column J. From Row 11.

The linked data is in another tab called "Equip ID" in the same file. I want to click that particular value in Column J cells and once i click it it should take me to tab " Equip ID" search it in column A of that tab and give me rows only with that value in coolumn A. Is it possible ?

 

[Fluff]

Yes, it's possible, but as it's a completely new question, could you please start a new thread?
Also you will need to give a bit more info.
Do you mean to autofilter Equip ID sheet?, if so specify where the header row is.