If cell value = C then current time in another cell.

[AKENNA431]

Hello,

I'm trying to create a checklist. There is a drop down menu on one end of the checklist that allows you to choose "C" to mark a particular item complete. I want the spread sheet to log the time each individual checklist item is complete. Currently I have in the cell that needs to show the time =IF(A5="C",NOW()," ") ...my intent here was if A5 is C then itll pop up with the current time in the cell showing that formula (G6.)...that formula works until you go down to the next checklist step and put a C in it's status block. It also puts the current time in its corresponding 'time complete' box but it also updates all other 'time complete' cells with the current time, instead of showing the time that they were completed. Is there a better way to approach this, either with formulas or Macros? Thanks.

AJ

 

[JoeMo]

You can't do it with formulas. You'll need a worksheet_change event macro.

 

[AKENNA431]

can you be a little more specific? What's the code to get it to detect the change in A5 and put the current time in G6?

 

[My Aswer Is This]

Are you sure you want to enter "C" in A5 and have time in G6

Would it not be A5 and G5

 

[AKENNA431]

I'm sure, merged cells and text in other cells and whatnot make it a little complicated. The Cell that needs to show the time is G6 when "C" is in A5, and when I change A10 to "C" I don't want G6 to go back and update with the current time again, I want it's time to stay...that's what I need. Looking for a little help.

 

[My Aswer Is This]

So we are only dealing with two ranges

A5 and G6

Nothing else

So is A6 has a C entered nothing happens. Correct?

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row > 1 Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value = "C" Then Target.Offset(-1, 6).Value = Time
End If
End Sub

 

[AKENNA431]

No A6 is merged with A5, A7, A8, and A9 to support text in adjacent cells. A10 is the next cell that will get a "C". and when there is a "C" in A10, G10 needs to show current time as well without affecting the time stamp in A6. If having the cells merged messes up the coding then I can fix that but the bottom line is A5 is going to get a "C" and the next cell to get a "C" will be A10. then A13, etc.

 

[My Aswer Is This]

Well if this is not going to be done for all cells in column A then we need some sort of trend here.

Is it every other row or every 5th row or what.

And is it always offset by one row as to where the time goes?

Like A10 G9

A50 G49

A65 G64

And none of the times will change after they are entered.

 

[AKENNA431]

actually, I can see the logic in that code you just provided, and if I change the checklist up to match your logic then it works perfectly. So that's what I'm going to do. Thanks a lot for your help!