Validation, how to see "today" date on top of the drop down list?

[Hoshyar]

I have a drop down list for the days of the year, form 1 jan 05 to 31 dec 05.
when I click on the drop down list, it always shows me 1 jan on the top of
the list, then I have to scrol down and look for "today" date. is there any
possibility that when I click an empty cell where the drop down list is
activated and see the "today" date? I mean when use it today I want to see
the date 16 Oct 05 on the top of the list, and if I use it tomorrow I want to
see 17 october 05 and so on.

Your help is appreciated.
Hoshyar

[Tom Ogilvy]

It shows items in the order they appear in the cells you reference (assume
you don't hard code them in the dialog).

If you don't want to show earlier dates, you can do this with a dynamic
defined name formula. (insert=>Name=>Define)

Name: List1
Refers to:
=Offset(Sheet1!$A$1,Match(Today(),Sheet1!$A$1:$A$366,0)-1,0,367-Match(Today(
),Sheet1!$A$1:$A$366,0),1)

Then in the data validation dialog, for source put in: =List1

(include the equal sign)

--
Regards,
Tom Ogilvy


"Hoshyar" <[email protected]> wrote in message
news:[email protected]...
> I have a drop down list for the days of the year, form 1 jan 05 to 31 dec
05.
> when I click on the drop down list, it always shows me 1 jan on the top of
> the list, then I have to scrol down and look for "today" date. is there
any
> possibility that when I click an empty cell where the drop down list is
> activated and see the "today" date? I mean when use it today I want to see
> the date 16 Oct 05 on the top of the list, and if I use it tomorrow I want
to
> see 17 october 05 and so on.
>
> Your help is appreciated.
> Hoshyar
>

[Hoshyar]

Tom,

Many thanks for your replay. in fact I do want to show earlier days as well,
but whenever I click on the cell where validation is activated, I want to see
"today" date and also the previous dates. Any more help is very much
appreciated.
Hoshyar

"Tom Ogilvy" wrote:

> It shows items in the order they appear in the cells you reference (assume
> you don't hard code them in the dialog).
>
> If you don't want to show earlier dates, you can do this with a dynamic
> defined name formula. (insert=>Name=>Define)
>
> Name: List1
> Refers to:
> =Offset(Sheet1!$A$1,Match(Today(),Sheet1!$A$1:$A$366,0)-1,0,367-Match(Today(
> ),Sheet1!$A$1:$A$366,0),1)
>
> Then in the data validation dialog, for source put in: =List1
>
> (include the equal sign)
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Hoshyar" <[email protected]> wrote in message
> news:[email protected]...
> > I have a drop down list for the days of the year, form 1 jan 05 to 31 dec
> 05.
> > when I click on the drop down list, it always shows me 1 jan on the top of
> > the list, then I have to scrol down and look for "today" date. is there
> any
> > possibility that when I click an empty cell where the drop down list is
> > activated and see the "today" date? I mean when use it today I want to see
> > the date 16 Oct 05 on the top of the list, and if I use it tomorrow I want
> to
> > see 17 october 05 and so on.
> >
> > Your help is appreciated.
> > Hoshyar
> >
>
>
>

[Tom Ogilvy]

Create a list of your dates going down the column, but start in the second
row. In the topmost cell put in the formula =today()

then use that list as the source for your dropdown.

--
Regards,
Tom Ogilvy

"Hoshyar" <[email protected]> wrote in message
news:[email protected]...
> Tom,
>
> Many thanks for your replay. in fact I do want to show earlier days as
well,
> but whenever I click on the cell where validation is activated, I want to
see
> "today" date and also the previous dates. Any more help is very much
> appreciated.
> Hoshyar
>
> "Tom Ogilvy" wrote:
>
> > It shows items in the order they appear in the cells you reference
(assume
> > you don't hard code them in the dialog).
> >
> > If you don't want to show earlier dates, you can do this with a dynamic
> > defined name formula. (insert=>Name=>Define)
> >
> > Name: List1
> > Refers to:
> >
=Offset(Sheet1!$A$1,Match(Today(),Sheet1!$A$1:$A$366,0)-1,0,367-Match(Today(
> > ),Sheet1!$A$1:$A$366,0),1)
> >
> > Then in the data validation dialog, for source put in: =List1
> >
> > (include the equal sign)
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Hoshyar" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a drop down list for the days of the year, form 1 jan 05 to 31
dec
> > 05.
> > > when I click on the drop down list, it always shows me 1 jan on the
top of
> > > the list, then I have to scrol down and look for "today" date. is
there
> > any
> > > possibility that when I click an empty cell where the drop down list
is
> > > activated and see the "today" date? I mean when use it today I want to
see
> > > the date 16 Oct 05 on the top of the list, and if I use it tomorrow I
want
> > to
> > > see 17 october 05 and so on.
> > >
> > > Your help is appreciated.
> > > Hoshyar
> > >
> >
> >
> >

[Hoshyar]

Many thanks Tom, it is very logical and it worked

regards
Hoshyar

"Tom Ogilvy" wrote:

> Create a list of your dates going down the column, but start in the second
> row. In the topmost cell put in the formula =today()
>
> then use that list as the source for your dropdown.
>
> --
> Regards,
> Tom Ogilvy
>
> "Hoshyar" <[email protected]> wrote in message
> news:[email protected]...
> > Tom,
> >
> > Many thanks for your replay. in fact I do want to show earlier days as
> well,
> > but whenever I click on the cell where validation is activated, I want to
> see
> > "today" date and also the previous dates. Any more help is very much
> > appreciated.
> > Hoshyar
> >
> > "Tom Ogilvy" wrote:
> >
> > > It shows items in the order they appear in the cells you reference
> (assume
> > > you don't hard code them in the dialog).
> > >
> > > If you don't want to show earlier dates, you can do this with a dynamic
> > > defined name formula. (insert=>Name=>Define)
> > >
> > > Name: List1
> > > Refers to:
> > >
> =Offset(Sheet1!$A$1,Match(Today(),Sheet1!$A$1:$A$366,0)-1,0,367-Match(Today(
> > > ),Sheet1!$A$1:$A$366,0),1)
> > >
> > > Then in the data validation dialog, for source put in: =List1
> > >
> > > (include the equal sign)
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Hoshyar" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have a drop down list for the days of the year, form 1 jan 05 to 31
> dec
> > > 05.
> > > > when I click on the drop down list, it always shows me 1 jan on the
> top of
> > > > the list, then I have to scrol down and look for "today" date. is
> there
> > > any
> > > > possibility that when I click an empty cell where the drop down list
> is
> > > > activated and see the "today" date? I mean when use it today I want to
> see
> > > > the date 16 Oct 05 on the top of the list, and if I use it tomorrow I
> want
> > > to
> > > > see 17 october 05 and so on.
> > > >
> > > > Your help is appreciated.
> > > > Hoshyar
> > > >
> > >
> > >
> > >
>
>
>

[Oldjay]

I hate to reply to this message with a question about another subject BUT I
don't know where else to go. I can't start a new thread. When I click "New"
then "Question" it doesn't give me a screen to post. It just goes back to the
original questions.



"Tom Ogilvy" wrote:

> It shows items in the order they appear in the cells you reference (assume
> you don't hard code them in the dialog).
>
> If you don't want to show earlier dates, you can do this with a dynamic
> defined name formula. (insert=>Name=>Define)
>
> Name: List1
> Refers to:
> =Offset(Sheet1!$A$1,Match(Today(),Sheet1!$A$1:$A$366,0)-1,0,367-Match(Today(
> ),Sheet1!$A$1:$A$366,0),1)
>
> Then in the data validation dialog, for source put in: =List1
>
> (include the equal sign)
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Hoshyar" <[email protected]> wrote in message
> news:[email protected]...
> > I have a drop down list for the days of the year, form 1 jan 05 to 31 dec
> 05.
> > when I click on the drop down list, it always shows me 1 jan on the top of
> > the list, then I have to scrol down and look for "today" date. is there
> any
> > possibility that when I click an empty cell where the drop down list is
> > activated and see the "today" date? I mean when use it today I want to see
> > the date 16 Oct 05 on the top of the list, and if I use it tomorrow I want
> to
> > see 17 october 05 and so on.
> >
> > Your help is appreciated.
> > Hoshyar
> >
>
>
>

[Tom Ogilvy]

news://msnews.microsoft.com/microsof...el.programming

put in the nagivation area of your browser with a return should open up
this group in Outlook express. then you just do New Post at the top.

--
Regards,
Tom Ogilvy


"Oldjay" <[email protected]> wrote in message
news:[email protected]...
> I hate to reply to this message with a question about another subject BUT
I
> don't know where else to go. I can't start a new thread. When I click
"New"
> then "Question" it doesn't give me a screen to post. It just goes back to
the
> original questions.
>
>
>
> "Tom Ogilvy" wrote:
>
> > It shows items in the order they appear in the cells you reference
(assume
> > you don't hard code them in the dialog).
> >
> > If you don't want to show earlier dates, you can do this with a dynamic
> > defined name formula. (insert=>Name=>Define)
> >
> > Name: List1
> > Refers to:
> >
=Offset(Sheet1!$A$1,Match(Today(),Sheet1!$A$1:$A$366,0)-1,0,367-Match(Today(
> > ),Sheet1!$A$1:$A$366,0),1)
> >
> > Then in the data validation dialog, for source put in: =List1
> >
> > (include the equal sign)
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Hoshyar" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a drop down list for the days of the year, form 1 jan 05 to 31
dec
> > 05.
> > > when I click on the drop down list, it always shows me 1 jan on the
top of
> > > the list, then I have to scrol down and look for "today" date. is
there
> > any
> > > possibility that when I click an empty cell where the drop down list
is
> > > activated and see the "today" date? I mean when use it today I want to
see
> > > the date 16 Oct 05 on the top of the list, and if I use it tomorrow I
want
> > to
> > > see 17 october 05 and so on.
> > >
> > > Your help is appreciated.
> > > Hoshyar
> > >
> >
> >
> >

[Oldjay]

Thanks - I knew that!! What a dummy. I remember talking to you many years ago
about a subject in your first book. You told me the subject would be in your
second book you were writing. How many years ago was that?
Oldjay

"Tom Ogilvy" wrote:

> news://msnews.microsoft.com/microsof...el.programming
>
> put in the nagivation area of your browser with a return should open up
> this group in Outlook express. then you just do New Post at the top.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Oldjay" <[email protected]> wrote in message
> news:[email protected]...
> > I hate to reply to this message with a question about another subject BUT
> I
> > don't know where else to go. I can't start a new thread. When I click
> "New"
> > then "Question" it doesn't give me a screen to post. It just goes back to
> the
> > original questions.
> >
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > It shows items in the order they appear in the cells you reference
> (assume
> > > you don't hard code them in the dialog).
> > >
> > > If you don't want to show earlier dates, you can do this with a dynamic
> > > defined name formula. (insert=>Name=>Define)
> > >
> > > Name: List1
> > > Refers to:
> > >
> =Offset(Sheet1!$A$1,Match(Today(),Sheet1!$A$1:$A$366,0)-1,0,367-Match(Today(
> > > ),Sheet1!$A$1:$A$366,0),1)
> > >
> > > Then in the data validation dialog, for source put in: =List1
> > >
> > > (include the equal sign)
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Hoshyar" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have a drop down list for the days of the year, form 1 jan 05 to 31
> dec
> > > 05.
> > > > when I click on the drop down list, it always shows me 1 jan on the
> top of
> > > > the list, then I have to scrol down and look for "today" date. is
> there
> > > any
> > > > possibility that when I click an empty cell where the drop down list
> is
> > > > activated and see the "today" date? I mean when use it today I want to
> see
> > > > the date 16 Oct 05 on the top of the list, and if I use it tomorrow I
> want
> > > to
> > > > see 17 october 05 and so on.
> > > >
> > > > Your help is appreciated.
> > > > Hoshyar
> > > >
> > >
> > >
> > >
>
>
>

[Simon Lloyd]

Hi,

If you have a list of dates on a sheet in the first cell at the top of the list type =Today(), that way the first in your list will always be todays date, use validation for a range of cells choose list then type =YOUR LIST NAME where your list name is what you have called your list of dates.

HTH

Simon