Selected Answer

Use the formula below to specify your named range *DROP_LST*.

`=List!$A$1:INDEX(List!$A$1:$A$1000,SUMPRODUCT(--(List!$A$1:$A$1000<>0)))`

Then specify *DROP_LST* as the source list for your validation dropdown. The difference between this and your formula is that the former would return a correct list even if the original contains intermittent blanks (zeroes).

This has to do with the difference of approach. COUNTIF produces a list starting from A1 for as many cells as there are non-zero values in A1:A100. In the worst case it would return a list of 10 blank cells (A1:A10) if there are non-zeroes only in A91:A100. In contrast, the SUMPRODUCT formula excludes zero values from the list wherever they are and only includes those which are not zero, also wherever they are.

Edit Jul 13, 2019 ===================================

My apologies! I have inadvertently mis-sold the above formula. The uncomfortable (for me) truth is that it works exactly like the one you have except that it uses another fucntion to calculate the number of entries in the list, SUMPRODUCT instead of COUNT. If your system of creating the list in column A can absolutely ensure that the zeroes are always at the bottom that's all you need. Either formula will do fine.

However, if you may have zeroes interspersed in column A it is possible to remove them. Use the formula below to create a list with the interspersed zeroes removed and then link that list to your validation dropdown in the manner you already know.

`=IFERROR(INDEX($A$2:$A$100,SMALL(IF(ISTEXT($A$2:$A$100),ROW($A$1:$A$99)), ROW(A1))),"")`

*INDEX($A$2:$A$100*

This is the range in which your original data are, including zeroes or non-text blanks. I intentionally moved this range down by one row to show that it could be anywhere.
*ISTEXT($A$2:$A$100)*

The range must be the same as above. The formula tests if it is text. Note that a formula like *=IF(B10=10,B10,"")* would create a text string "" if B10<>10. Your current system produces a zero which isn't text.
*ROW($A$1:$A$99)*

This produces a series of numbers from 1 to 100. It must always start with 1 and must have as many numbers as the data range has cells.
*ROW(A1)*

This also produces a series of numbers starting with 1. How far it counts is determined by the other ranges. It's essential to start with 1 (therefore "A1").
- Enter the formula as an array formula, confirming with Control+Shift+Enter in place of the customary singular Enter. When entered correctly Excel will display it surrounded by curly braces in the formula bar. Else, it will show no result.
- Copy the formula down for as many rows as your data range has, including zeroes.
- Test by deleting any of the values near the top of the original list. Items below move up.