+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Hoshyar
    Guest

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

    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


  2. #2
    Tom Ogilvy
    Guest

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

    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" <Hoshyar@discussions.microsoft.com> wrote in message
    news:C3F725CC-46C8-4DE7-9F99-BB0056EAEF04@microsoft.com...
    > 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
    >




  3. #3
    Hoshyar
    Guest

    Re: Validation, how to see "today" date on top of the drop down li

    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" <Hoshyar@discussions.microsoft.com> wrote in message
    > news:C3F725CC-46C8-4DE7-9F99-BB0056EAEF04@microsoft.com...
    > > 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
    > >

    >
    >
    >


  4. #4
    Tom Ogilvy
    Guest

    Re: Validation, how to see "today" date on top of the drop down li

    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" <Hoshyar@discussions.microsoft.com> wrote in message
    news:FF894258-72E2-412E-9ED3-C02C9207835E@microsoft.com...
    > 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" <Hoshyar@discussions.microsoft.com> wrote in message
    > > news:C3F725CC-46C8-4DE7-9F99-BB0056EAEF04@microsoft.com...
    > > > 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
    > > >

    > >
    > >
    > >




  5. #5
    Hoshyar
    Guest

    Re: Validation, how to see "today" date on top of the drop down li

    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" <Hoshyar@discussions.microsoft.com> wrote in message
    > news:FF894258-72E2-412E-9ED3-C02C9207835E@microsoft.com...
    > > 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" <Hoshyar@discussions.microsoft.com> wrote in message
    > > > news:C3F725CC-46C8-4DE7-9F99-BB0056EAEF04@microsoft.com...
    > > > > 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
    > > > >
    > > >
    > > >
    > > >

    >
    >
    >


  6. #6
    Oldjay
    Guest

    Re: Validation, how to see "today" date on top of the drop down li

    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" <Hoshyar@discussions.microsoft.com> wrote in message
    > news:C3F725CC-46C8-4DE7-9F99-BB0056EAEF04@microsoft.com...
    > > 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
    > >

    >
    >
    >


  7. #7
    Tom Ogilvy
    Guest

    Re: Validation, how to see "today" date on top of the drop down li

    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" <Oldjay@discussions.microsoft.com> wrote in message
    news:6304CCE6-9894-4A84-A31D-D19E4E3E9EA0@microsoft.com...
    > 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" <Hoshyar@discussions.microsoft.com> wrote in message
    > > news:C3F725CC-46C8-4DE7-9F99-BB0056EAEF04@microsoft.com...
    > > > 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
    > > >

    > >
    > >
    > >




  8. #8
    Oldjay
    Guest

    Re: Validation, how to see "today" date on top of the drop down li

    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" <Oldjay@discussions.microsoft.com> wrote in message
    > news:6304CCE6-9894-4A84-A31D-D19E4E3E9EA0@microsoft.com...
    > > 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" <Hoshyar@discussions.microsoft.com> wrote in message
    > > > news:C3F725CC-46C8-4DE7-9F99-BB0056EAEF04@microsoft.com...
    > > > > 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
    > > > >
    > > >
    > > >
    > > >

    >
    >
    >


  9. #9
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1