To set Data Validation for Years and Months

[ravi123]

Hi,

I'm at biggner level excel usage and trying to set data validation for Years (Primary Validation) and months (secondary validation). Wishes to select year from drop-down list and then relevant months, however unable to do this. I don't know what i am doing wrong on earth . Also want to get secondary validation (months) to be dynamic in nature so that only previous or current month showing not the coming months of 2017.

 

[oldbrewer]

year

month

today's date

24/02/2017

months

mylist

2017

jan

month name

feb

01/01/2017

jan

jan

01/02/2017

feb

feb

01/03/2017

mar

01/04/2017

apr

01/05/2017

may

01/06/2017

jun

01/07/2017

july

01/08/2017

aug

01/09/2017

sep

B2 is a drop down using mylist

01/10/2017

oct

01/11/2017

nov

mylist only populates if the dates below months

01/12/2017

dec

are earlier than K1 (todays date)

<colgroup><col span="9"><col><col><col span="3"><col><col span="3"></colgroup><tbody>
</tbody>

 

[jorismoerings]

In addition:

The data validation in D2 simply holds the year range; the data validation in D3 holds the formula as shown in E3.

Hope this helps

Excel 2016 (Windows) 64 bit
	A	B	C	D	E
1	Year	Month		DV1	DV2
2	2013	1		2016	2
3	2014	2			2
4	2015	3
5	2016	4
6	2017	5
7		6
8		7
9		8
10		9
11		10
12		11
13		12
Sheet1

Cell Formulas
Range	Formula
E3	=IF(YEAR(TODAY())=D2,OFFSET(B2,0,0,MONTH(TODAY()),1),B2:B13)

 

[ravi123]

Hi Oldbrewer,

Thanks to bother my question and assisting me.

 

[ravi123]

In addition:

The data validation in D2 simply holds the year range; the data validation in D3 holds the formula as shown in E3.

Hope this helps

Excel 2016 (Windows) 64 bit

	A	B	C	D	E
1	Year	Month		DV1	DV2
2	2013	1		2016	2
3	2014	2			2
4	2015	3
5	2016	4
6	2017	5
7		6
8		7
9		8
10		9
11		10
12		11
13		12

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E3 =IF(YEAR(TODAY())=D2,OFFSET(B2,0,0,MONTH(TODAY()),1),B2:B13) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Hi Jorismoerings,

Thanks for stopping by and reverting with exact solution. I'm grateful to you for addressing my query precisely. Will like to ask you one more query, why we have used width as "1" in OFFSET function?

 

[jorismoerings]

why we have used width as "1" in OFFSET function?

I'm usually trying to have the function explain itself; with the OFFSET i'm creating an array which has the elements in the rows hence using the HEIGHT parameter and has 1 column of data hence using the WIDTH. But it's an optional parameter so you can leave it out.