Calc, filter value from cell range?

asked 2016-01-12 02:21:47 +0200

LibreGuy gravatar image

updated 2016-01-12 02:24:18 +0200

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?

edit retag flag offensive close merge delete