Data Validation Drop Down - Formula

[neodjandre]

I have column A with entries and column B where some rows have the value "J".

I want to create a data validation drop down list formula which only shows entries in column A which have the value "J" in column B.

Can't figure it out... any help please?

many thanks

 

[Scott T]

Off to the side or you can put it on a different/hidden sheet. put this formula in. This is an array formula and must be committed with CTRL+SHIFT+ENTER.

=IF(ROWS(J$1:J1)>COUNTIF($B$2:$B$18,"j"),"",INDEX($A$2:$A$18,SMALL(IF($B$2:$B$18="j",ROW($B$2:$B$18)-ROW($B$2)+1),ROWS($J$1:J1))))

In the next column assuming you put the array formula in J1

=LEN(J1)

Copy both formulas down.

In data validation choose list and use this formula

=OFFSET(J1,0,0,COUNTIF(K1:K18,">0"))

 

[neodjandre]

hmm ok, this is very helpful though a bit cumbersome, I was assuming there would be an easier way out!

 

[Yongle]

This is the easy way....

 

[neodjandre]

ok I managed to do it in the end! but is it possible to sort the results alphabetically with the array formula or do I need to do this in another column ?

many thanks again