Conditionally insert date and fix it.

  xania 08:57 21 Jan 2008
Locked

I am designing s spreadsheet of student achievement and need to store in the final column the date when the student achieves the final product. I have stored TODAY()in a separate filed called Today> and used the IF statement IF(M3=10,Today,"Outstanding")> where M3 is the count of the number of modules passed. This does, indeed, insert the current date in the cell, but, of course, the following day, it modifies the contents acordingly. What I need is to have the inserted date fixed at the date it is inserted. Any ideas, please?

  VoG II 09:10 21 Jan 2008

For the example of M3=10 where does the date have to go. What triggers M3 to equal 10?

  Picklefactory 10:31 21 Jan 2008

If I'm understanding correctly, you have a manually updated cell for number of modules passed (eg A1) and when it gets to 10, another cell (eg B1) automatically enters todays date. But you want B1 to stay fixed at that date and not update again on following or consecutive days. I think I found what you need but VoG™ might need to explain how to modify it, if he might be so kind. Try this

On the sheet you are working on - right click on the tab and select view code - then paste the following code

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Column
Case 1
If Trim(Target.Value) <> "" Then
ActiveSheet.Cells(Target.Row, 2) = Now
End If
End Select
End Sub


and close the Vb editor window.

On the sheet now, you will be able to enter a value in column A and the current date will appear in column B - ( and it will not change when you open the file the next day).
(Thanks go to JV0710 on Mr Excel, where I found this)

  Picklefactory 10:39 21 Jan 2008

I think that should be
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Column
Case 13
If Trim(Target.Value) = 10 Then
ActiveSheet.Cells(Target.Row, 14) = Now
End If
End Select
End Sub

If I'm correct, that would now insert todays date in column N if value in column M = 10.

  VoG II 10:45 21 Jan 2008

I was thinking along the same lines

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 13 Then
Application.EnableEvents = False
If Target.Value = 10 Then Target.Offset(0, 1).Value = Date
Application.EnableEvents = True
End If
End Sub


However, both your code and mine will only fire if 10 is manually entered in column M. If the value is for example the sum of values in other columns, and a change in one of those columns causes M3 to equal 10 then they won't run. Which is why I asked my second question above.

  Picklefactory 10:49 21 Jan 2008

That's about my limit, and I don't profess to have written that code. I'm still at the level where I can 'jiggle' it a bit, but have to find basic code from such experts as yourself. (Or, in this case, JV0710 on Mr Excel).
Over to you, I'm afraid.

  xania 11:25 21 Jan 2008

VOG

Picklefactory has the problem right - M3 is the sum of 10 preceeding columns of modules and each field will change from 0 to 1 as that module is completed. Once the 10 modules are completed, I need the date to be recorded. Unfortunately his solution does not work so still need help.

Picklefactory

You are right about the limitations of this code - as column M is a calculated field, your solution will not work.

  VoG II 11:34 21 Jan 2008

Assuming that the values change from 0 to 1 by typing in rather than being pulled in from an external database then right click the sheet tab, select View Code, copy and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column >= 3 And Target.Column <= 12 Then
If Range("M" & Target.Row).Value = 10 Then
Application.EnableEvents = False
Range("N" & Target.Row).Value = Date
Application.EnableEvents = True
End If
End If
End Sub


then close the code window.

  xania 08:47 22 Jan 2008

Spot on VOG. Many thanks. I really must get to grips with VB!!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

50 best online Adobe XD tutorials

iPad Pro 10.5in (2017) review

Comment connecter un MacBook à une TV ?