Excel Conditional Dynamic Data Validation

0
votes

I have a problem with the formula defining list for Data validation.

I am using an array formula in range D1:D5 to list values from B1:B5 that match criteria "Y" in column A:

=IFERROR(INDEX($B$1:$B$5,SMALL(IF($A$1:$A$5="Y",ROW($A$1:$A$5),""),ROW())),"") I name this array "myrange"

Then in E1 in Data validation I use write below formula for a list:

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

However, if I do the same starting not in row 1 - formula does not show correct values (some values disappear and "Y" criteria is not maintained). Please, help me to fix this if you know how.

PS. Above formulas come from: Excel Conditional Dynamic Data Validation

2
arraysexcel
"If I do the same starting not in row 1", do you mean the =IFERROR function or the =OFFSET data validation? - chancea
I mean IFERROR Function. - Andrey Vorobyev
F.E. If my ranges start in A5:A10, B5:B10 and I type formula in D5 as =IFERROR(INDEX($B$5:$B$10,SMALL(IF($A$5:$A$10="Y",ROW($A$5:$A$10),""),ROW())),"") - Andrey Vorobyev
I get wrong values that are not in order and not matching the criteria "Y" - Andrey Vorobyev

2 Answers

0
votes

The formula 

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

Only works at row 1 because you are indexing using SMALL(...,ROW()) which ROW() obviously returns the current row you are at. This works great if your list starts at row 1 and works down but will break if started at row 2 or lower.

Depending on where you put the data you need to update that ROW() number to match the actual row that you start on.

In addition to make this formula more dynamic you can use the full column name i.e. $E:$E or $B:$B to make it so that wherever the data starts in the column this formula will pick it up.

You would need to update the formula to 

 =IFERROR(INDEX($E:$E,SMALL(IF($D:$D="aa",ROW($D:$D),""),ROW(A1))),"")

So instead of ROW() we call ROW(A1) which evalutates to 1 and as you autofill down since we omit the $ it will update to A2, A3, A4... as such you get your nice {1,2,3,4,5} array as you need it.

Now remember to use ctrl+shift+enter only on the first entry. Then auto-fill down and name the range. If you select the whole range and then hit ctrl-shift-enter it will not work.

And as long as you keep myrange updated then your list should work fine.

0
votes

So to sum it up, You would need to update the formula to

=IFERROR(INDEX($E:$E,SMALL(IF($D:$D="aa",ROW($D:$D),""),ROW(A1))),"")

Now remember to use ctrl+shift+enter only on the first entry. Then auto-fill down and name the range. If you select the whole range and then hit ctrl-shift-enter it will not work.

You can increase Efficiency by limiting ranges in the formula, but always start at row 1, e.g.: =IFERROR(INDEX($E1:$E10,SMALL(IF($D1:$D10="aa",ROW($D1:$D10),""),ROW(A1))),"") even if you type the formula in row 5,6,7... Thank you @chancea