Excel Conditional Dynamic Data Validation

2
votes

I have a minor problem while playing with Excel. Here is the problem definition:

I want conditional data validation for below table:

Column 1  Column 2
Y         Res1
Y         Res2
N         Res3
N         Res4
Y         Res5

From above table, I need a cell dropdown(using data validation) with the list of values from Column 2 who has 'Y' value in Column 1. Is it possible? If yes, could you please help me out. Your help will be appreciated.

Please note: I dont want to write Excel VBA for that, through VBA I am able to do so.. But requirement is to do with formulas.

2
excelvalidation

2 Answers

1
votes

Just use this array formula, and then copy down:

=INDEX($B$1:$B$5;SMALL(IF($A$1:$A$5="Y";ROW($A$1:$A$5)-ROW($A$1)+1;"");ROWS($A$1:A1)))

Dont forget to Ctrl Shift Enter

So change the formula to

=IFERROR(INDEX($B$1:$B$5;SMALL(IF($A$1:$A$5="Y";ROW($A$1:$A$5)-ROW($A$1)+1;"");ROWS($A$1:A1)));"")

Imagine you're writing the formula in D1, copy down till the maximum possible matches (for instance D1:D100 and name this range myrange) copy till the end (till you got at least an empty cell). Now write this formula in Source for List DataValidation:

=OFFSET(D1;0;0;MATCH("";myrange;0)-1;1)
1
votes

Thanks CRondao and Fagun...

Here is the final answer, I am writing in separate, as there is already more debate happened. So user, without wasting time in reading comments, can directly come here, and read answer...

Select 100 or more rows, press F2, and enter below formula,

=IFERROR(INDEX($B$1:$B$5,SMALL(IF($A$1:$A$5="Y",ROW($A$1:$A$5),""),ROW())),"")

And then press "Ctrl+Shift+Enter". Define name of range from Name manager with myrange for 100 or more cells.

Select E1 cell, and in Data validation write below formula for List:

=OFFSET(D1;0;0;MATCH("";myrange;0)-1;1)