Conditional data validation

Zubair

Active Member
Joined
Jul 4, 2009
Messages
336
Office Version
  1. 2016
Platform
  1. Windows
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

ViewScenario by year
Number of Scenario


D1:E7
Number of Scenario Number of Years
Scenario 1Y1
Scenario 2Y2
Scenario 3Y3
Scenario 4Y4
Scenario 5Y5
Scenario 6Y6
Scenario 7Y7
Scenario 8Y8
Scenario 9Y9
Scenario 10Y10
 
Last edited:
Hello! If correctly understood, then insert this formula into B2
Excel Formula:
=INDEX($E$2:$E$6;MATCH($A2;$D$2:$D$6;0))
 
Upvote 0
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
ABCDE
1ViewScenario by yearNumber of ScenarioNumber of Years
2Number of ScenarioScenario 1Scenario 1Y1
3Scenario 2Y2
4Scenario 3Y3
5Scenario 4Y4
6Scenario 5Y5
7Scenario 6Y6
8Scenario 7Y7
9Scenario 8Y8
10Scenario 9Y9
11Scenario 10Y10
Sheet1
Cells with Data Validation
CellAllowCriteria
A2List=$D$1:$E$1
B2List=OFFSET($D$1,1,MATCH($A$2,$D$1:$E$1,0)-1,10,1)


Book1
ABCDE
1ViewScenario by yearNumber of ScenarioNumber of Years
2Number of YearsY1Scenario 1Y1
3Scenario 2Y2
4Scenario 3Y3
5Scenario 4Y4
6Scenario 5Y5
7Scenario 6Y6
8Scenario 7Y7
9Scenario 8Y8
10Scenario 9Y9
11Scenario 10Y10
Sheet1
Cells with Data Validation
CellAllowCriteria
A2List=$D$1:$E$1
B2List=OFFSET($D$1,1,MATCH($A$2,$D$1:$E$1,0)-1,10,1)
 
Upvote 0
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
ABCDE
1ViewScenario by yearNumber of ScenarioNumber of Years
2Number of ScenarioScenario 1Scenario 1Y1
3Scenario 2Y2
4Scenario 3Y3
5Scenario 4Y4
6Scenario 5Y5
7Scenario 6Y6
8Scenario 7Y7
9Scenario 8Y8
10Scenario 9Y9
11Scenario 10Y10
Sheet1
Cells with Data Validation
CellAllowCriteria
A2List=$D$1:$E$1
B2List=OFFSET($D$1,1,MATCH($A$2,$D$1:$E$1,0)-1,10,1)


Book1
ABCDE
1ViewScenario by yearNumber of ScenarioNumber of Years
2Number of YearsY1Scenario 1Y1
3Scenario 2Y2
4Scenario 3Y3
5Scenario 4Y4
6Scenario 5Y5
7Scenario 6Y6
8Scenario 7Y7
9Scenario 8Y8
10Scenario 9Y9
11Scenario 10Y10
Sheet1
Cells with Data Validation
CellAllowCriteria
A2List=$D$1:$E$1
B2List=OFFSET($D$1,1,MATCH($A$2,$D$1:$E$1,0)-1,10,1)
 
Upvote 0
Hi 9999 - You understand correctly and your given solution is perfectly working on my file, many thanks.
 
Upvote 0
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 ?
 
Upvote 0
That's possible, with VBA - is that something you'd consider?
 
Upvote 0

Forum statistics

Threads
1,226,840
Messages
6,193,277
Members
453,788
Latest member
drcharle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top