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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]_2021[/td][td]Q2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]data Validation
Allow: List
Source:
=Years[/td][td]data Validation
Allow: List
Source:
=INDIRECT(A1)[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#DDEBF7]_2015[/td][td=bgcolor:#DDEBF7]_2016[/td][td=bgcolor:#DDEBF7]_2017[/td][td=bgcolor:#DDEBF7]_2018[/td][td=bgcolor:#DDEBF7]_2019[/td][td=bgcolor:#DDEBF7]_2020[/td][td=bgcolor:#DDEBF7]_2021[/td][td=bgcolor:#DDEBF7]_2022[/td][td][/td][td]Years[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#DDEBF7]Q1[/td][td=bgcolor:#DDEBF7]Q1[/td][td=bgcolor:#DDEBF7]Q2[/td][td=bgcolor:#DDEBF7]Q3[/td][td=bgcolor:#DDEBF7]Q1[/td][td=bgcolor:#DDEBF7]Q1[/td][td=bgcolor:#DDEBF7]Q1[/td][td=bgcolor:#DDEBF7]Q1[/td][td][/td][td]_2015[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td=bgcolor:#DDEBF7]Q2[/td][td=bgcolor:#DDEBF7]Q2[/td][td=bgcolor:#DDEBF7]Q4[/td][td=bgcolor:#DDEBF7]Q4[/td][td=bgcolor:#DDEBF7]Q2[/td][td=bgcolor:#DDEBF7]Q2[/td][td=bgcolor:#DDEBF7]Q2[/td][td=bgcolor:#DDEBF7]Q2[/td][td][/td][td]_2016[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Q3[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Q3[/td][td=bgcolor:#DDEBF7]Q3[/td][td=bgcolor:#DDEBF7]Q3[/td][td=bgcolor:#DDEBF7]Q3[/td][td][/td][td]_2017[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Q4[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Q4[/td][td=bgcolor:#DDEBF7]Q4[/td][td=bgcolor:#DDEBF7]Q4[/td][td=bgcolor:#DDEBF7]Q4[/td][td][/td][td]_2018[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]_2019[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]_2020[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]_2021[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]_2022[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Names[/td][/tr][/table]

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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]

2016​

[/td][td]Q3[/td][td][/td][td]_2016[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td][/td][td]data Validation
Allow: List
Source: =INDIRECT(D1)[/td][td][/td][td]Formula in D1="_"&A1[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]