Hide Zeroes in Dropdown List?

[c.clavin]

Hi guys, I'm running into a wall with this. Is there a way for a dropdown list to not return entries with zeroes? I have something like Product A, Product B, 0, Product C, 0. I'd like to have the zeroes not show up, is there anything I can add to my formula to get them out of there? Thanks!!

This is my formula I'm using right now:
=OFFSET('SELECTED OPT'!$N$1,0,0,COUNTA('SELECTED OPT'!$N:$N),1)

 

[cmowla]

First of all, OFFSET is a volatile function which can slow down performance (Volatile Excel Functions -Decision Models). An alternative to what you're trying to accomplish is to use INDEX. Therefore, see if the following works for you.

=LET(rng,'SELECTED OPT'!$N$1:INDEX('SELECTED OPT'!$N:N,COUNTA('SELECTED OPT'!$N:$N)),FILTER(rng,rng<>0))

 

[Peter_SSs]

Assuming that you are referring to a Data Validation drop-down list ..

An option that I would consider would be to produce the list elsewhere (I have used column Z on 'SELECTED OPT' but it could be anywhere). I have used 1000 but use any number that will be sure to be big enough for any data that you might have in column N.

c.clavin.xlsm
	N	O										Y	Z
1	Product A												Product A
2	Product B												Product B
3	0												Product C
4	Product C
5	0
6
SELECTED OPT

Cell Formulas
Range	Formula
Z1:Z3	Z1	=FILTER(N1:N1000,N1:N1000<>0)
Dynamic array formulas.

Then in your Data Validation ..

c.clavin.xlsm
	C
1
2
3
4
5
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
C1:C5	List	='SELECTED OPT'!Z1#

 

[c.clavin]

This one worked great, thanks. I did not know about the volatile functions either. Very good to know. Thanks guys!

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.