Excel Conditional Dynamic Data Validation

Question

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.

CRondao · Accepted Answer · 2014-02-28 11:05:53Z

1

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)

edited Feb 28, 2014 at 11:05

answered Feb 28, 2014 at 9:28

CRondao

1,9012 gold badges12 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Fagun Safi Over a year ago

Yeah I have that formula, but my final output should be in Drop Down, which I am not able to do that... Thats why I have used Data Validation keyword. And even If, I use this formula in Name List, still I am not getting in that dropdown, since it's an array formula, while Data validation needs a list... Anyway Thanks for answer :)

CRondao Over a year ago

So I think you should explain better when you're asking a question... :)

Fagun Safi Over a year ago

Hello, Thanks a lot... :) But seems that your second formula is not working, it will give same value for each of 100 cells.. Anyway that I have taken care of by changing a bit in formula: =IFERROR(INDEX($B$1:$B$5,SMALL(IF($A$1:$A$5="Y",ROW($A$1:$A$5),""),ROW())),"")... After that I used ur 3rd formula and it works... Thanks again... :)

Fagun Safi Over a year ago

I have just replaced ROWS($A$1:A1) with Row(), because when I use your array formula, for all 100 cells, it will take same value as ROWS($A$1:A1) is not getting incremented.

CRondao Over a year ago

It should work, it is an array formula but it's only to use in one cell, then copy down for the others. Tried just now it works. But, the important part, you solve your problem.... :)

Fagun Safi · Accepted Answer · 2014-03-03 05:59:31Z

1

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)

answered Mar 3, 2014 at 5:59

Fagun Safi

611 silver badge6 bronze badges

1 Comment

dmshow Over a year ago

This is a great help. The only thing that I would change is get rid of the IFERROR. There are some times that you will have to use IFERROR, but it has to calculate for each cell. Use a helper cell with the number of items to return, and use IF(CHECK>helpercell, "", formulaFromAnswer). This will speed up the calculation.

Collectives™ on Stack Overflow

Excel Conditional Dynamic Data Validation

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related