Should be easy...Auto Date Fill

[-emma-]

Hey,

This should be easy but I have been struggling with it for a while
now.

A1 = A Validation List with the options "Y" or "N".
A2 = The date (dd/mm/yyyy) appears ONLY "Y" was imput in A1. When "N"
selected in A1, "N/A" apprears in A2.

Once A2 has been filled it needs to stay the same date and not change
to the current date (Which what I have had to stick with for the time
being ) I have battled with =NOW() and =TODAY() but all auto update
each day which isnt what im after. The N/A is unimportant...all that
really matters is the "Y" activating todays date and staying that days
date.

Mail me for screen shots or futher information if needed.

Any help offered at all would be a great help!

Thanks,

Emma x

 

[Ardus Petus]

Tools>Options>Calculation check Iteration
enter in A2:
=IF(A1="N",NA(),IF(ISNUMBER(A2),A2,TODAY()))

HTH

 

[-emma-]

Thanks for this but it only leaves me what I have already. I saved
while it was showing 14/07/2006...waited til past 12 midnight and when
I went back in it was displying 15/07/2006. I need to to stay on the
same date I made the selection in A1.

Any other ideas please?

Thanks,

Emma x

 

[Guest]

ctrl and : gives you todays date and stays that date
otherwise a liitle macro that copies today() and then pastes as a value

 

[Gord Dibben]

Emma

For a static date or N/A.............

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Address = "$A$1" Then
If Target.Value = "Y" Then
Excel.Range("A2").Value = Now
Else: Excel.Range("A2").Value = "N/A"
End If
End If
enditall:
Application.EnableEvents = True
End Sub

This is sheet event code.

Right-click on the sheet tab and "View Code"

Copy/paste into that module.


Gord Dibben MS Excel MVP

Thanks for this but it only leaves me what I have already. I saved
while it was showing 14/07/2006...waited til past 12 midnight and when
I went back in it was displying 15/07/2006. I need to to stay on the
same date I made the selection in A1.

Any other ideas please?

Thanks,

Emma x

Gord Dibben MS Excel MVP

 

[Dave Peterson]

ctrl-;
(control-semicolon)
gives the date.

ctrl-shift-; (or ctrl-
(control-colon)
gives the time