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

 

[Zubair]

ok, please share the VBA code if possible

 

[kevin9999]

Actually, I've had an idea of doing it without VBA - using conditional formatting instead. Leave it with me.

 

[Zubair]

Thanks in Advance

 

[kevin9999]

How about this. Using Conditional Formatting, when you change the dropdown in A2 from Number of Scenario to Number of Years (and vice versa) the font in B2 turns white and the cell fills yellow to prompt the user to make a selection.

From this:

test.xlsm
	A	B	C	D	E
1	View	Scenario by year		Number of Scenario	Number of Years
2	Number of Scenario	Scenario 4		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 Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	Expression	=COUNTIF(OFFSET($D$1,1,MATCH($A$2,$D$1:$E$1,0)-1,10,1),$B$2)=0	text	NO

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)

To this:

test.xlsm
	A	B	C	D	E
1	View	Scenario by year		Number of Scenario	Number of Years
2	Number of Years	Scenario 4		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 Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	Expression	=COUNTIF(OFFSET($D$1,1,MATCH($A$2,$D$1:$E$1,0)-1,10,1),$B$2)=0	text	NO

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)

Then this:

test.xlsm
	A	B	C	D	E
1	View	Scenario by year		Number of Scenario	Number of Years
2	Number of Years	Y3		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 Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	Expression	=COUNTIF(OFFSET($D$1,1,MATCH($A$2,$D$1:$E$1,0)-1,10,1),$B$2)=0	text	NO

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)

No VBA required, and you can change the fill colour to whatever you prefer.

 

[Zubair]

Good idea, yes I will do this, 2ndly after changing selection the heading I have linked will not appear so user can automatically understand that I need to change B2.

Many thanks