Data validation: List of values in an array excluding values that equal ""

[Danmallon]

Hi, I have an automatically updated list going down a column.

(100 rows in the column have a formula and it is basically saying it equals the next figure in the list that has been selected until there are no others and it then ="").

=IF(ROWS($L$3:L3)<=(COUNTIF('Purchases Library'!$F:$F,$B$9)),INDEX('Purchases Library'!$B:$B,SMALL(IF('Purchases Library'!$F:$F=$B$9,ROW('Purchases Library'!$F:$F)),ROWS($L$3:L3))),"")

and the list differs depending on what I have selected in a different cell (B9). This list can have as little as 2 items in it and up to 100, otherwise it ="". I want to then have a DATA VALIDATION LIST that chooses everything in that array (up to 100) except for things that = "".

Is this possible? Any help with this would be greatly appreciated

 

[bschwartz]

Hi,

Try custom data validation instead of list, and use this formula:

=ISNUMBER(MATCH(A2,B:B,0))

Red cell ​should be the cell you want the validation in.

 

[Danmallon]

Thanks Ben.

I used that idea (unfortunately what you said didn't work perfectly in my situation) and altered it to work for my situation.

In the DATA VALIDATION LIST Source: I put in =OFFSET(L3 (cell where the 1st formula was stored), 0,0,MATCH("",L3:L103 (array with my formulas), 0)-1)