Pull data to drop down list only if cell value is yes

[Neste]

ColumnA   ColumnB                ColumnC                        ColumnD                        ColumnE
[B]ProdID	ProdName	             ProdDesc	                  Active?		               For DropDownList[/B]
ID1	        Item1	             ProdDesc1	                  Yes		                       Item1
ID2	        Item2	             ProdDesc2	                  Yes		                       Item2
ID3	        Item3	             ProdDesc3	                  Yes		                       Item3
ID4	        Item4	             ProdDesc4	                  No		                       Item6
ID5	        Item5	             ProdDesc5	                  No		                       Item7
ID6	        Item6	             ProdDesc6	                  Yes		                       Item8
ID7	        Item7	             ProdDesc7	                  Yes		                       Item10
ID8	        Item8	             ProdDesc8	                  Yes		
ID9	        Item9	             ProdDesc9	                  No		
ID10	        Item10	             ProdDesc10	                  Yes		
ID11	        Item11	             ProdDesc11	                  No

Hi really stuck with some excel functions, would need some help from the good people here

So i have managed to make a formula like this in ColumnE:
=IFERROR(INDEX($B$1:$B$12;SMALL(IF($D$1:$D$12<>"No";ROW($B$1:$B$12));ROW($B1)+1));"")

And I have a drop down list with this forumla:

=OFFSET($F$2;0;0;COUNTA($F:$F)-1)

What I want to do here is only pull data from ColumnB if it is Activ? in Column D and get that in to Data Validation Drop down list without any blanks and alos if more Products are added, Drop down list should auto update.

So get from Column B if ColumnD is "yes".
Display result in Data Validation Drop Down list.
Take away blanks and auto update new Product values.

Thank you for helping.

 

[kvsrinivasamurthy]

Try This formula for drop down list

[COLOR=#333333]=OFFSET($E$2;0;0;COUNTIF($E:$E,"*?")-1)[/COLOR]

 

[Neste]

That trick did it here I have been sweting for hours hehe, thank you m8