Calc, filter value from cell range? [closed]

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 reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-08-28 20:50:21.962691