Data validation with OFFSET + COUNTIF


I have a list od values referenced from another workbook. The empty values are shown as 0s. I want to build a data validation drop-down and this code works ok (by clicking on the code and pressing F9 I can see the correct result) but when applied in the data validation dialogue I still am presented with a list with 0s. The aim is to have a dynamic list, i.e. when a new value is enterd in the data workbook it will also be available in the present workbook, this is necessary as the same data is shared by several workbooks


The list is named DROP_LST

The attached workbook's referenced values in col A (='C:\Users\XXX\Documents\YYY\[Vars.xlsx]Vars'!$A2) will be lost 

So the question is: How to get a list without the 0? I.e. restrict the list to the actual values? Thanks



Selected Answer

Use the formula below to specify your named range DROP_LST.


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))),"")
  1. 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.
  2. 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.
  3. 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.
  4. 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").
  5. 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.
  6. Copy the formula down for as many rows as your data range has, including zeroes.
  7. Test by deleting any of the values near the top of the original list. Items below move up.


Works beautifully, thank you very much. 
The other user's answer also works fine, I am at a loss as to who give the best answer
Michael_Php (rep: 4) Jul 12, '19 at 5:15 am
Thanks for selecting my answer :-)
Variatus (rep: 3728) Jul 12, '19 at 5:36 am
;P haha
don (rep: 1775) Jul 12, '19 at 8:30 pm
Thank you very much for the update, I'll implement it, but the formula you supplyed earlier works fine anyway as my system puts zeroes at the bottom of the list. But I'll give it a try with the updated one. Cheers
Michael_Php (rep: 4) Jul 14, '19 at 7:48 am
Add to Discussion

So, basically, names can be your friend but they often decide to stab you in the back if you're not using absolute references :P

Your name is having its formula changed and it is not aligning with the correct range, in this case A1:A100.

The solution is to use absolute references in your name like this:


Otherwise, the A1:A100 part within the COUNTIF() gets changed. 

Try this formula and see how it works!



Thank you so very much. The formula actually works best like this 
As with 
 it showed blanks
The other user's answer also works fine, I am at a loss as to who give the best answer
Michael_Php (rep: 4) Jul 12, '19 at 5:14 am
You are right. The "<>0" is necessary. With it, the formula based on COUNTIF might work a little faster but its essential that your system can ensure that all zeroes are always at the end of the list.
Variatus (rep: 3728) Jul 12, '19 at 5:36 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login