applying Drop down list on cells based on condition

[ajl_ahmed]

applying Drop down list on cells based on condition

I have these columns which represent the subjects of each stage based on the defined semester. I want to apply data validation on cells (B6,B7,B8 and B9) such that:

- if the stage is First and the semester is first, (item1, item2, and item 3) will appear in the drop-down list of cell B6.
- if the stage is Second and the semester is Second, (item10, item11, and item 12) will appear in the drop-down list of cell B9.

and so on

is it possible to do that? please write the details of the procedure
Thanks

[#2 · Accepted solution]

@ajl_ahmed 

First, select the range G4:J7.

On the Formulas tab of the ribbon, click Create from Selection.

Make sure that only the check box Top Row is ticked, then click OK.

This creates named ranges First_First, First_Second etc. (Slashes / aren't allowed in a name, so they are replaced with underscores _).

Then select B6:B9. Create data validation of type List with source =INDIRECT($C6&"_"&$D6)

See the attached version.