DropDown list of Column A based on Column B of a table

**aarunb88** · 07-01-2013, 04:21 PM

Hi All,

I have a table like

Test No Validity
1 TRUE
2 TRUE
3 FALSE
4 TRUE
5 FALSE
6 FALSE

I want to create a dropdown which shows only the 'Test No' whose validity is TRUE. In this case only 1,2,4
The list should dnamically change when i change the validity.

**vlady** · 07-01-2013, 10:40 PM

extract the values that meets the criteria "yes" then from those extracted values create the validation.

look sample attachment.

validation list.xlsx

**aarunb88** · 07-02-2013, 04:54 AM

Thanks a ton vlady... \m/

**aarunb88** · 07-02-2013, 09:44 AM

Dear vlady,
Could you please explain the usage SMALL(IF(yes="yes",ROW(sam)-ROW($A$2)+1),ROWS($E$2:E2)) esp the what you intend to do with ROW(sam)-ROW($A$2)+1 in the formula: IFERROR(INDEX(sam,SMALL(IF(yes="yes",ROW(sam)-ROW($A$2)+1),ROWS($E$2:E2))),"")

My brain is too tiny to understand this.

Thanks in advance.

**vlady** · 07-02-2013, 07:38 PM

I have trouble explaining things but try my best

yes and sam are the named ranges

IF(yes="yes" -> array returns true or false
ROW(sam)-ROW($A$2)+1 -> ROW(sam) get array count position row 2,3,4,5,6 -ROW($A$2) subtract 2 come 0,1,2,3,4 +1 drops 0 add one position becomes 1,2,3,4,5

if statement then compares the values of sam that became true and false to the position 1,2,3,4,5 in the sample that is true,true,false,false,true so 1,2,false,false,5

ROWS($E$2:E2) gets how many cells are there so 1
1 becomes 1st smallest in the function small(range,1)

small({1,2,false,false,5},1

when you drag down ROWS($E$2:E3) so there are 2 cells small(range,2)