hi,
i am using libre calc for past 1 week. i use spreadsheets a lot for my work. and filter optioon is the key for my workings. but in filter i find no way to filter my results using the row colors.
can anyone please help me out regarding the filter of rows using colors?
thanks in advance
@KarthikVAS - This reads as if you used another spreadsheet software before. I won’t know that one. Maybe it offers an option to filter by colours, LibreOffice Calc does not, as far as I know. There are reasons. If you are interested in the reasons I see, and in ways to do filtering in connection with colours in Calc, please tell me.
From this vid - starting from a little past 1:00 - the appeal of ‘filter by color’ should be clear.
I’m thinking the developers here are overthinking this. As for my own use, especially during tax season, I prepare rough spreadsheeted records and want to flag various items to follow up on, or to move elsewhere. Fill colors work great for this - but I need to filter them later…
I spent the time to watch that clip just to learn that it shows a few colored cells, but doesn’t mention filtering based on colors. If I missed something, please explain!
What actually is shown is sorting ascending and an automatized version of filtering for which you apply the tools in Calc via Data > Sort ascending and Data > AutoFilter respectively.
If you are actually interested in filtering based on color, please read first my old answer below and the post linked-in there. →
I used up 10 min then to find out that you rather wanted to point to this clip. At about 3 min there is demonstrated filtering by color. This is only an additional way in the example to filter for Yes|No|Maybe contents.
The design of that sheet was not that bad to code the respective information only by color.
LibreOffice doesn’t support this mistake at all.
Hi! There is a solution. Make a special macro function CELL_BACKCOLOR(SHEET;ROW;COLUMN) with the help of this thread It’s qute easy. Here is a sample of usage. After opening a saved document or after changing anything related, these function need a hard recalculation (Ctrl+Shift+F9).
This is an urgent advice to not use the “introspective” functions as a means for the functionality of spreadsheets. Exclusively use them to support a redesign of your sheets dedicating cells (coiumns) to contain the respective information explicitly. “Villeroy”, the author of the functions you pointed to, would also strongly advise so.
Fortunately there is progress now and then.
Unfortunately there is progress in the wrong direction rather frequently.
See , tdf#76258 , tdf#144662
Unfortunately development sometimes follows the “a thousand users can’t err” approach.
Are you talking of the users believing in the dogma that MS can’t err?
The overlapping intersection cut set is very large.
I think there is no option, please see related threads about the matter.
Another related topic is found here in the openoffice.org forum. The first paragraph of the first post there is explaining in short the need for such features as an indicator of bad design in a spreadsheet. Villeroy, the author of that post, also supplies BASIC code helping in the rectification, and advice concerning its usage.
I attach an example handling the colours and the filtering by an additional explicit information:ask46352FilteringByColour001.ods
Hi - The macro solution is probably the most efficient. But I can suggest a workaround…
If the color is applied by styles, and if the number of styles is limited, you can use “unused” properties of styles to differentiate them.
In the example attached the format of the styles are different.
These attributes can be tested by the CELL function with a formula such:
=IF(CELL("COLOR";A3)=1;"Yellow";IF(CELL("PROTECT";A3)=0;"Orange";IF(CELL("FORMAT";A3)=",0";"Red";IF(CELL("PARENTHESES";A3)=1;"Green";"Default"))))