Conditional data validation

[Zubair]

Hi team,
I wanted to add conditional formatting on cell B2 based on A2 from array D2:E7, means drop down will show list from D2:D7 if cell A2 = "Number of Scenario" in a same way if A2 = "Number of Years" the list in data validation will show E2:E7.

Please help with formula.

A1:B2

View	Scenario by year
Number of Scenario

D1:E7

Number of Scenario	Number of Years
Scenario 1	Y1
Scenario 2	Y2
Scenario 3	Y3
Scenario 4	Y4
Scenario 5	Y5
Scenario 6	Y6
Scenario 7	Y7
Scenario 8	Y8
Scenario 9	Y9
Scenario 10	Y10

 

[Sergius]

Hello! If correctly understood, then insert this formula into B2

=INDEX($E$2:$E$6;MATCH($A2;$D$2:$D$6;0))

 

[kevin9999]

I interpret the request somewhat differently. If I'm right, you want to select in A2 via data validation Number of Years or Number of Scenario - which will lead to a different data validation choice list in cell B2? Something like this perhaps:

Book1
	A	B	C	D	E
1	View	Scenario by year		Number of Scenario	Number of Years
2	Number of Scenario	Scenario 1		Scenario 1	Y1
3				Scenario 2	Y2
4				Scenario 3	Y3
5				Scenario 4	Y4
6				Scenario 5	Y5
7				Scenario 6	Y6
8				Scenario 7	Y7
9				Scenario 8	Y8
10				Scenario 9	Y9
11				Scenario 10	Y10
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=$D$1:$E$1
B2	List	=OFFSET($D$1,1,MATCH($A$2,$D$1:$E$1,0)-1,10,1)

Book1
	A	B	C	D	E
1	View	Scenario by year		Number of Scenario	Number of Years
2	Number of Years	Y1		Scenario 1	Y1
3				Scenario 2	Y2
4				Scenario 3	Y3
5				Scenario 4	Y4
6				Scenario 5	Y5
7				Scenario 6	Y6
8				Scenario 7	Y7
9				Scenario 8	Y8
10				Scenario 9	Y9
11				Scenario 10	Y10
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=$D$1:$E$1
B2	List	=OFFSET($D$1,1,MATCH($A$2,$D$1:$E$1,0)-1,10,1)

 

[Zubair]

I interpret the request somewhat differently. If I'm right, you want to select in A2 via data validation Number of Years or Number of Scenario - which will lead to a different data validation choice list in cell B2? Something like this perhaps:

Book1
	A	B	C	D	E
1	View	Scenario by year		Number of Scenario	Number of Years
2	Number of Scenario	Scenario 1		Scenario 1	Y1
3				Scenario 2	Y2
4				Scenario 3	Y3
5				Scenario 4	Y4
6				Scenario 5	Y5
7				Scenario 6	Y6
8				Scenario 7	Y7
9				Scenario 8	Y8
10				Scenario 9	Y9
11				Scenario 10	Y10
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=$D$1:$E$1
B2	List	=OFFSET($D$1,1,MATCH($A$2,$D$1:$E$1,0)-1,10,1)

Book1
	A	B	C	D	E
1	View	Scenario by year		Number of Scenario	Number of Years
2	Number of Years	Y1		Scenario 1	Y1
3				Scenario 2	Y2
4				Scenario 3	Y3
5				Scenario 4	Y4
6				Scenario 5	Y5
7				Scenario 6	Y6
8				Scenario 7	Y7
9				Scenario 8	Y8
10				Scenario 9	Y9
11				Scenario 10	Y10
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=$D$1:$E$1
B2	List	=OFFSET($D$1,1,MATCH($A$2,$D$1:$E$1,0)-1,10,1)

 

[Zubair]

Hi 9999 - You understand correctly and your given solution is perfectly working on my file, many thanks.

 

[kevin9999]

Hi 9999 - You understand correctly and your given solution is perfectly working on my file, many thanks.

Happy to help, and thanks for the feedback

 

[Zubair]

Happy to help, and thanks for the feedback

While changing number of years from Number of scenario, last selection in B2 can show blank, so user can not forget to select number of years in B2 ?

 

[kevin9999]

That's possible, with VBA - is that something you'd consider?

 

[Zubair]

That's possible, with VBA - is that something you'd consider?

I prefer without VBA if possible.

 

[kevin9999]

Unfortunately, I can't think of a way of doing it without VBA.