Data Validation - Number of days based on year and moth values

[Legacy 143009]

Hi,

I want to create a data validation dropdown list but I have never created dependent before.

Well, in a support sheet I have a column of Years, a column of Months and a column of Days to 31. In my main sheet I want the user to pick a date with three dropdowns. But I want to limit the items of Days list depending on the Year and Month.

So, I found this:
=DAY(EOMONTH(date,0))

But I don't know how to implement this to data validation. Days options shouldn't show items more than this value. Any suggestions?

Thanks!

 

[Marcelo Branco]

Maybe something like this

Assumes the dropdowns in Sheet1
A2 to select the year
B2 to select the month (numeric)
C2 to select the day

	A​	B​	C​	D​	E ​
1​	Year​	Month​	Day​		Days​
2​	2016​	2​			1​
3​					2​
4​					3​
5​					4​
6​					5​
7​					6​
8​					7​
9​					8​
10​					9​
11​					10​
12​					11​
13​					12​
14​					13​
15​					14​
16​					15​
17​					16​
18​					17​
19​					18​
20​					19​
21​					20​
22​					21​
23​					22​
24​					23​
25​					24​
26​					25​
27​					26​
28​					27​
29​					28​
30​					29​
31​					30​
32​					31​

<tbody>
</tbody>

Create a named range containing the days 1 to 31 and name it Days (E2:E32 - gray area)

Select C2 (Important)

Let's create a named formula:
Formulas > Names Manager > New
Name: DaysList
Refers to: =INDEX(Days,1):INDEX(Days,DAY(EOMONTH(DATE(Sheet1!$A2,Sheet1!$B2,1),0)))


With C2 still selected
Data > Data Validation > List
Source: DaysList

Change the Year(A2) and the Month(B2) to test

Hope this helps

M.

 

[kvsrinivasamurthy]

J2= YEAR

K2= MONTH

Formula in L2 then down to L33 for DATE.

=IF(ROWS($L$2:$L2)=1,1,IF(ROWS($L$2:$L2)>DAY(EOMONTH(DATE($J$2,$K$2,1),0)),"",L1+1))

Formula for date valiation

=$L$2:INDEX($L$2:$L$33,COUNT($L$2:$L$33))

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Legacy 143009]

Ah, then the main idea is to filter the days in helper column. Let me try on the computer later.