Data validation dependent list for year and quarter

[harinsh]

HI Team,

I need some help in data validation. I have years in column "A" like 2010,2011,2012,2013 so on ...and in column "B" I have quarters like Q1,Q2,Q3 so on....so, each year against quarters will be listed and not necessary every time 4 quarters some year should have 1 or 2 quarters as well.

I need to create data validation where if I select 2010 then only quarters should available to select in second validation. Hence, I need two validation ..one for year and another for quarter depend on year.

Can anyone help on this..thank you,

 

[Yongle]

Here is one way

Create a named range for each year and use INDIRECT function in the data validation list formula in A2
- name of range cannot be a number, so I decided to prefix each year with underscore character "_"

Excel 2016 (Windows) 32 bit

	A​	B​
1​	_2021	Q2
2​	data Validation Allow: List Source: =Years	data Validation Allow: List Source: =INDIRECT(A1)

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	_2015	_2016	_2017	_2018	_2019	_2020	_2021	_2022		Years
2​	Q1	Q1	Q2	Q3	Q1	Q1	Q1	Q1		_2015
3​	Q2	Q2	Q4	Q4	Q2	Q2	Q2	Q2		_2016
4​		Q3			Q3	Q3	Q3	Q3		_2017
5​		Q4			Q4	Q4	Q4	Q4		_2018
6​										_2019
7​										_2020
8​										_2021
9​										_2022

Sheet: Names

Create named ranges quickly
Select A1:H5
Formulas tab\ Create Names From Selection \ from Top Row
Which automatically creates 8 named ranges _2015 _2016 _2017 _2018 _2019 _2020 _2021 _2022

Create named range Years containing those values (column J)

on other sheet

A1
data Validation
Allow: List
Source: =Years

A2
data Validation
Allow: List
Source: =INDIRECT(A1)

 

[Yongle]

If you want to avoid prefix in A1 use an intermediate cell (which could be hidden from user view)

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​
1​	2016​	Q3		_2016
2​		data Validation Allow: List Source: =INDIRECT(D1)		Formula in D1="_"&A1

Sheet: Sheet1