Create two different drop downs depending on the value of a cell on another sheet

[drop05]

Hello, i wanted to see if there is a way to have two different drop down on one cell (D5) with four different choices for each drop down depending on a value of another cell in a different sheet?
Drop down one will have: "Test 1, Test 2, Test 3, Blank", If on sheet "Guide" on cell A1 = "First"
if the value in cell a1 on sheet Guide is "Second" then the drop down will be "Class 1, Class 2, Class 3, Not Selected"

is there a way to do this in excel?

 

[Toadstool]

Hi Drop05,

Try

Book1
	A	B	C	D	E
1	Dropdown			First	Second
2				Test 1	Class 1
3				Test 2	Class 2
4				Test 3	Class 3
5				Blank	Not Selected
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=OFFSET($D$1,1,MATCH(Guide!$A$1,$D$1:$E$1,0)-1,4)

 

[Peter_SSs]

Another way that you could consider, especially if the lists are different length (as in my changed sample below) is to make the lists named ranges.

drop05_2.xlsm
	A
1	Second
Guide

F2:F5 below is named "First", G2:G6 is named "Second"

drop05_2.xlsm
	D	E	F	G
1			First	Second
2			Test 1	Class 1
3			Test 2	Class 2
4			Test 3	Class 3
5			Blank	Class 4
6				Not Selected
7
Sheet2

Cells with Data Validation
Cell	Allow	Criteria
D5	List	=INDIRECT(Guide!A1)