How to perform data validation without empty values?

Question

excel

There is a list that I get by the formula =IF($P$10=Price!$L$2;Price!L3;IF($P$10=Price!$N$2;Price!N3;IF($P$10=Price!$P$2 ;Price!P3;IF($P$10=Price!$R$2;Price!S3;IF($P$10=Price!$T$2;Price!T3;
Specify the correct material type))))) the number of elements is 52, but in some columns there are 20.
I check the data for this list (by the maximum number), some values ​​remain zero, how to get rid of them.
I also tried to transfer this list with the following formula = IF (ETEXT (AJ5); AJ5; ""), so that Excel sees the null cell, but alas, data validation still sees it as a string (indicated that Ignore empty cells)
Tell me, can someone met .
Avoid macros

Answer 1

It's a little unclear what the question is without an example.
In general, so many IF() nestings as a check for the value of one cell usually indicate that something went wrong.