Using different lists depending on month

[Jo4x4]

Hi everybody,

I have a workbook where Sheet1 is used for adding details of customers. Part of that is a drop-down list (stored on sheet2) with dates ie: 1 August 2011, 2 August 2011 etc.

Is there a way to have all the dates for the year on sheet 2, and then let the drop-down list only reflect the current month?

Thanks in advance
Jo

Win XP, Excel 2007

 

[bebo021999]

Hi everybody,
I have a workbook where Sheet1 is used for adding details of customers. Part of that is a drop-down list (stored on sheet2) with dates ie: 1 August 2011, 2 August 2011 etc.
Is there a way to have all the dates for the year on sheet 2, and then let the drop-down list only reflect the current month?
Thanks in advance
Jo
Win XP, Excel 2007

In sheet2, in A1, you type: 01/01/2011
A2 = A1+1
Fill down till get 31/12/2011
Ctrl-F3, named "month" with refer to:

=OFFSET($A$1,MATCH(MONTH(TODAY()),MONTH($A$1:$A$365),0)-1,,SUM(IF(MONTH($A$1:$A$365)=MONTH(TODAY()),1,0)),)

back to sheet 1, at your cell, choose Data/ Data Validation/Allow:List/Source: = month
Now you can pick date with your current month.
Any file attachment required, ctc me at quangphanidico@yahoo.com

 

[Jo4x4]

Thanks Bebo

Jo