Search code examples
excelexcel-formulaexcel-2010excel-2007

How to Display months based on the Quarter Selection

I have 2 columns in Excel sheet, Column1 has the Quarter Selection dropdown box(Q1, Q2, Q3 and Q4). And Column2 has the month selction dropdown box.

I need help in the below.

Q1 - Feb, March, April

Q2 - May, Jun, July

Q3 - Aug, Sep, Oct

Q4 - Nov, Dec, Jan

Now if a user selects Quarter as Q2 in Column1, the Dropdown values in Column2 has to have only May, June and July.

I tried the grouping option but it doesn't help me.

Thanks in Advance,

Satish D

Solution

  • You should create two tables, one containing quarters, and other containing all quarters with corresponding months (tables are surrounded with thick border).

    enter image description here

    Then in cell A2 create simple data validation and set list in D1:D4 as source. In cell B1 also create data validation list, set allow to list, and in formula area please enter this formula =OFFSET(INDIRECT(ADDRESS(MATCH($A$1,E1:E12,0),5)),0,1,3,1)

    You data validation list in cell B1 is now dependent on selection in list A1.