Drop-down lists

  • Thread starter Thread starter David Stricklen
  • Start date Start date
D

David Stricklen

Is there anyway possible to create a Drop-down list that includes the dates of only the current month? If it's March I would like column A to only have March 1st through the 31st to be possible entries. When it becomes April, then April dates are only ones allowed, and so on. If someone could help that would be great!

David
 
Hi David

One way is to have a working column. This holds the current month and this
is linked to the data validaton drop down list.

Now how do you fill the list:

use =date(year(now()),month(now()),1..31) but
the problems occur on 29/30/31
and all you do is test the month to see if it is different from day 1 (day 1
is in a2):
a2:a29=DATE(YEAR(NOW()),MONTH(NOW()),1.28
a30=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),29))=MONTH(A2),DATE(YEAR(NOW()),MONTH(NOW()),29),""
a31=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),30))=MONTH(A2),DATE(YEAR(NOW()),MONTH(NOW()),30),""
a32=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),31))=MONTH(A2),DATE(YEAR(NOW()),MONTH(NOW()),31),"")
and then you link data vaidation to this list.
 
You need to make a new worksheet called Sheet2 with the following formula in cell a1 (top left) giving the first day in the current month:
=DATE(YEAR(TODAY()),MONTH(TODAY()),1)
then in the cell below (a2)
=A1+1
drag A2 all the way down till A32. You should have all the days in the current month plus 1 or 2 from next months

in cell B29 enter the following
=DAY(A29)
drag B29 down till B32

You now need to create a dynamic range which will change according to the days in the current month to do this create a named range called DATES where the refers to is the following:
=OFFSET(Sheet2!$A$1,0,0,MATCH(1,Sheet2!$B$29:$B$32,0)+27,1)

in the cell you require the dropdown you format it using data validation set toplist where the list source is DATES

Perhaps someone else on this board can explain better than I but it does work

Good luck

PS if you tell me which version of excel you are using, I'll email you a working version
Is there anyway possible to create a Drop-down list that includes the dates of only the current month? If it's March I would like column A to only have March 1st through the 31st to be possible entries. When it becomes April, then April dates are only ones allowed, and so on. If someone could help that would be great!

David
 
Create a list for your dropdown with the first cell formula being:

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

In Row 2 enter: =H1+1 and copy down to Row 28

In Row 29 enter:

=IF(MONTH(H28+1)=MONTH(H28),H28+1,"")

and copy down to Row 31

Use that list in your Data validation.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Is there anyway possible to create a Drop-down list that includes the dates of only the current month? If it's March I would like column A to only have March 1st through the 31st to be possible entries. When it becomes April, then April dates are only ones allowed, and so on. If someone could help that would be great!

David
 
This is very similar (and a lot simpler) to my proposed solution but with the drawback that you allow blanks to be entered in months with less than 31 days.
Create a list for your dropdown with the first cell formula being:

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

In Row 2 enter: =H1+1 and copy down to Row 28

In Row 29 enter:

=IF(MONTH(H28+1)=MONTH(H28),H28+1,"")

and copy down to Row 31

Use that list in your Data validation.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Is there anyway possible to create a Drop-down list that includes the dates of only the current month? If it's March I would like column A to only have March 1st through the 31st to be possible entries. When it becomes April, then April dates are only ones allowed, and so on. If someone could help that would be great!

David
 
You're Very welcome. Thank for the feedback

--


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk



This is very similar (and a lot simpler) to my proposed solution but with the drawback that you allow blanks to be entered in months with less than 31 days.
Create a list for your dropdown with the first cell formula being:

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

In Row 2 enter: =H1+1 and copy down to Row 28

In Row 29 enter:

=IF(MONTH(H28+1)=MONTH(H28),H28+1,"")

and copy down to Row 31

Use that list in your Data validation.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Is there anyway possible to create a Drop-down list that includes the dates of only the current month? If it's March I would like column A to only have March 1st through the 31st to be possible entries. When it becomes April, then April dates are only ones allowed, and so on. If someone could help that would be great!

David
 
Back
Top