Hi, probably a simple question for the experts, but I can’t find the answer
In a column I use this formula (validity option) to create a drop down list.
IFERROR(OFFSET(A$1;0;0;ROW(A1)-ROW(A$1);1);"")
Due to the use of formulas this specific column contains zero values, meaning some of the cells contain the value “0”. I hide these zero values by using a style option. However, in the drop down list the 0 value reappears. Is there a way to prevent this?
So what I’m actually looking for (if it exists) is a way to adjust the above formula so it filters all zeros. So if the column looks something like this …
A
1 red
2 0
3 blue
4 0
5 orange
… then the formula should only detect the values ‘red’, ‘blue’ and ‘orange’ and skip the zeros. Is that possible?