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
--
AP

"-emma-" <[email protected]> a écrit dans
le message de news: [email protected]...
>
> 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
>
>
> --
> -emma-
> ------------------------------------------------------------------------
> -emma-'s Profile:
> http://www.excelforum.com/member.php...o&userid=36378
> View this thread: http://www.excelforum.com/showthread...hreadid=561653
>

[-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

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

HTH
--
AP

"-emma-" <[email protected]> a écrit dans
le message de news: [email protected]...
>
> 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
>
>
> --
> -emma-
> ------------------------------------------------------------------------
> -emma-'s Profile:
> http://www.excelforum.com/member.php...o&userid=36378
> View this thread: http://www.excelforum.com/showthread...hreadid=561653
>

[paul]

ctrl and : gives you todays date and stays that date
otherwise a liitle macro that copies today() and then pastes as a value
--
paul
[email protected]
remove nospam for email addy!



"-emma-" wrote:

>
> 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
>
> Ardus Petus Wrote:
> > Tools>Options>Calculation check Iteration
> > enter in A2:
> > =IF(A1="N",NA(),IF(ISNUMBER(A2),A2,TODAY()))
> >
> > HTH
> > --
> > AP
> >
> > "-emma-" <[email protected]> a écrit
> > dans
> > le message de news:
> > [email protected]...
> > >
> > > 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
> > >
> > >
> > > --
> > > -emma-
> > >
> > ------------------------------------------------------------------------
> > > -emma-'s Profile:
> > > http://www.excelforum.com/member.php...o&userid=36378
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=561653
> > >
>
>
> --
> -emma-
> ------------------------------------------------------------------------
> -emma-'s Profile: http://www.excelforum.com/member.php...o&userid=36378
> View this thread: http://www.excelforum.com/showthread...hreadid=561653
>
>

[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


On Fri, 14 Jul 2006 19:04:54 -0400, -emma-
<[email protected]> wrote:

>
>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
>
>Ardus Petus Wrote:
>> Tools>Options>Calculation check Iteration
>> enter in A2:
>> =IF(A1="N",NA(),IF(ISNUMBER(A2),A2,TODAY()))
>>
>> HTH
>> --
>> AP
>>
>> "-emma-" <[email protected]> a écrit
>> dans
>> le message de news:
>> [email protected]...
>> >
>> > 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
>> >
>> >
>> > --
>> > -emma-
>> >
>> ------------------------------------------------------------------------
>> > -emma-'s Profile:
>> > http://www.excelforum.com/member.php...o&userid=36378
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=561653
>> >

Gord Dibben MS Excel MVP

[Dave Peterson]

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

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

paul wrote:
>
> ctrl and : gives you todays date and stays that date
> otherwise a liitle macro that copies today() and then pastes as a value
> --
> paul
> [email protected]
> remove nospam for email addy!
>
> "-emma-" wrote:
>
> >
> > 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
> >
> > Ardus Petus Wrote:
> > > Tools>Options>Calculation check Iteration
> > > enter in A2:
> > > =IF(A1="N",NA(),IF(ISNUMBER(A2),A2,TODAY()))
> > >
> > > HTH
> > > --
> > > AP
> > >
> > > "-emma-" <[email protected]> a écrit
> > > dans
> > > le message de news:
> > > [email protected]...
> > > >
> > > > 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
> > > >
> > > >
> > > > --
> > > > -emma-
> > > >
> > > ------------------------------------------------------------------------
> > > > -emma-'s Profile:
> > > > http://www.excelforum.com/member.php...o&userid=36378
> > > > View this thread:
> > > http://www.excelforum.com/showthread...hreadid=561653
> > > >
> >
> >
> > --
> > -emma-
> > ------------------------------------------------------------------------
> > -emma-'s Profile: http://www.excelforum.com/member.php...o&userid=36378
> > View this thread: http://www.excelforum.com/showthread...hreadid=561653
> >
> >

--

Dave Peterson