How can I automatically vary print areas depending on cell contents? i.e. I want to omit areas which have no result, or result is zero, leaving only >0 cells on the printout.
quick and dirty workaround: use conditional formatting, setting the text color to white for cells not > 0?
Thanks tohuwawohu but that only works on screen to make cells appear blank. The printout/sheet is still not reduced in length at all. Any other ideas?
The obvious way is to filter out those lines (which works on screen and on print), but filtering is currently very buggy (3.5.3.2) and also cells, once filtered out, are hard to get back! I want to toggle the filtering on and off between users over time.
Hi @rogerggbr! If there are specific bugs in the filtering that are annoying to you, please file a bug so that we can try to fix them for the next release: https://www.libreoffice.org/get-help/bug
Thanks @qubit1. I don’t have a specific bug - partly because I don’t fully understand how the filtering is supposed to work, and partly because it is different to the Excel version I am converting from.
In Excel, a cell is deemed “empty” if the VALUE is zero. In LO, if a cell contains a formula it is not empty and can’t be used to filter! Is this a bug or simply by design? It is certainly a big difference causing me a headache and trying to find a workaround.
The solution I have come up with is to use filtering. First allocate a column, off the printed area, which will be used for the filter. Each row which may be required to be omitted has the following formula:
=IF(C16>0,“Print”,“NoPrint”)
Rows which are always required just contain “Print”, pasted in manually.
(C16 is just an example of course, in my case it is the cell which contains the part quantity. If there is a quantity of zero I don’t want to see the row)
Obviously cells with numbers return “Print”, but cells containing text also return “Print”, which makes it easy.
This column can now be selected for filtering (select the whole area first, then “Autofilter” - it seems easiest). As there are only 2 choices in this column, it is easy to switch on and off as required.