How to ignore zero values in list for data validation

[mikehk]

I have a horizontal list, that has blank cells. How do I ignore blank cells in data validation.

I use Microsoft 365.

Please help.

Thanks.

[AliGW]

An oft asked question - lots of threads on it here.

Have a look here: https://www.contextures.com/xlDataVal08.html

[mikehk]

Thanks for the link, please help me with this
formula as it shows blank cells.



=OFFSET(B1,0,0,1,COUNTIF(RawDataYear,"<>"))

I have created a name manager for cells
$B$1:$AH$1

cell B1 has number C1 is empty D1 has number E1 is empty ...so on.

Thanks.

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

[mikehk]

Please find sample sheet attached.

[AliGW]

Your thread title is misleading - please change it. The items you wish to ignore in the list are zero values, not blanks. Thanks.

[mikehk]

Oh ya ....ignore the zero values. How to do that?
Thread title changed.

Thanks.

[mikehk]

this formula works
=FILTER(B1:AG1,B1:AG1>2000)

if used with OFFSET formula gives me a #VALUE error
=OFFSET(B1,0,0,1,COUNTIF(FromYear,(FILTER(B1:AG1,B1:AG1>2000))))

I attach the sample sheet. Please help.

Thanks.

[JeteMc]

Perhaps the following will help.
Produce a list in XFD1:XFD45 using:

Please Login or Register  to view this content.

The source for the data validation is:

Please Login or Register  to view this content.

Let us know if you have any questions.

[mikehk]

It is way over my understanding but it works!

thank you

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.