I have a drop-down list (Data/Validation) that contains dates from the
beginning of the fiscal year (10-01-2004) to the end of the fiscal year
(09-30-2005). Is there a way to have the drop down start at the current date
instead at the beginning on October 01? Thanks.

One way:

In a second sheet, enter

A1: =TODAY()
A2: =A1 + 1

Format A2 as a date, and copy down to A366. Choose Insert/Name/Define
and enter

Name in workbook: DateList
Refers To:
=OFFSET(Sheet2!$A$1,0,0,365+(DAY(Sheet2!$A$1)<DAY (Sheet2!$A$366)),1)

Then in your validation cell, use

Allow: List
Source: =DateList


In article ,
"Jan Buckley" wrote:

I have a drop-down list (Data/Validation) that contains dates from the
beginning of the fiscal year (10-01-2004) to the end of the fiscal year
(09-30-2005). Is there a way to have the drop down start at the current date
instead at the beginning on October 01? Thanks.

JE: I was thrilled when, using your instructions, I got this to work.
However, I now find that ,not only can I not back up in the list to a date
prior to TODAY(), neither can I type in a previous date. Since there are
times when it will be necessary to enter an earlier date (previous to
TODAY()), is there a solution to this? I sure hope so - setting the date to
the current date is very convenient and a time saver, but I won't be able to
use it if I can't 'back up'. Thanks so much.
Jan


"JE McGimpsey" wrote:

One way:

In a second sheet, enter

A1: =TODAY()
A2: =A1 + 1

Format A2 as a date, and copy down to A366. Choose Insert/Name/Define
and enter

Name in workbook: DateList
Refers To:
=OFFSET(Sheet2!$A$1,0,0,365+(DAY(Sheet2!$A$1)<DAY (Sheet2!$A$366)),1)

Then in your validation cell, use

Allow: List
Source: =DateList


In article ,
"Jan Buckley" wrote:

I have a drop-down list (Data/Validation) that contains dates from the
beginning of the fiscal year (10-01-2004) to the end of the fiscal year
(09-30-2005). Is there a way to have the drop down start at the current date
instead at the beginning on October 01? Thanks.

AFAIK, there's no way to get the validation list to pop up in the middle
of the list.

One workaround would be to create a Combobox from the control toolbox on
the worksheet that would dynamically load the FY's dates, and preselect
today's date:

Enter this code behind it (right-click the combobox and choose View
Code):

Private Sub ComboBox1_Gotfocus()
Dim dDates() As Date
Dim nFY As Long
Dim i As Long

nFY = Year(Date + 92)
ReDim dDates(0 to DateSerial(nFY, 12, 31) - DateSerial(nFY, 1, 0))
dDates(0) = DateSerial(nFY - 1, 10, 1)
For i = 1 To UBound(dDates)
dDates(i) = dDates(0) + i
Next i
With ComboBox1
.List = dDates
.ListIndex = Date - dDates(0)
End With
End Sub

Private Sub ComboBox1_Click()
Range("A1").Value = ComboBox1.Value
End Sub

In article ,
"Jan Buckley" wrote:

JE: I was thrilled when, using your instructions, I got this to work.
However, I now find that ,not only can I not back up in the list to a date
prior to TODAY(), neither can I type in a previous date. Since there are
times when it will be necessary to enter an earlier date (previous to
TODAY()), is there a solution to this? I sure hope so - setting the date to
the current date is very convenient and a time saver, but I won't be able to
use it if I can't 'back up'. Thanks so much.
Jan

JE:
I typed in the code behind the combo box as instructed, but it doesn't work.
When I move my cursor over the box, the little four-sided black arrows appear
and I can't click into it. Also, does the A1 in the code (Private Sub
ComboBox1_click()) refer to the DateList we constructed yesterday? Do I need
to keep that list? I'm not a programmer as I'm sure you've been able to
devise. . . . Help!

"JE McGimpsey" wrote:

AFAIK, there's no way to get the validation list to pop up in the middle
of the list.

One workaround would be to create a Combobox from the control toolbox on
the worksheet that would dynamically load the FY's dates, and preselect
today's date:

Enter this code behind it (right-click the combobox and choose View
Code):

Private Sub ComboBox1_Gotfocus()
Dim dDates() As Date
Dim nFY As Long
Dim i As Long

nFY = Year(Date + 92)
ReDim dDates(0 to DateSerial(nFY, 12, 31) - DateSerial(nFY, 1, 0))
dDates(0) = DateSerial(nFY - 1, 10, 1)
For i = 1 To UBound(dDates)
dDates(i) = dDates(0) + i
Next i
With ComboBox1
.List = dDates
.ListIndex = Date - dDates(0)
End With
End Sub

Private Sub ComboBox1_Click()
Range("A1").Value = ComboBox1.Value
End Sub

In article ,
"Jan Buckley" wrote:

JE: I was thrilled when, using your instructions, I got this to work.
However, I now find that ,not only can I not back up in the list to a date
prior to TODAY(), neither can I type in a previous date. Since there are
times when it will be necessary to enter an earlier date (previous to
TODAY()), is there a solution to this? I sure hope so - setting the date to
the current date is very convenient and a time saver, but I won't be able to
use it if I can't 'back up'. Thanks so much.
Jan

You need to exit Design Mode (by default, the first control on the
Control Toolbox toolbar).

No, with the code I gave you, there's no need to use Date_List any
longer. The "A1" referred to the cell you want the date to appear in.

In article ,
"Jan Buckley" wrote:

I typed in the code behind the combo box as instructed, but it doesn't work.
When I move my cursor over the box, the little four-sided black arrows appear
and I can't click into it. Also, does the A1 in the code (Private Sub
ComboBox1_click()) refer to the DateList we constructed yesterday? Do I need
to keep that list? I'm not a programmer as I'm sure you've been able to
devise. . . . Help!