# calc: show or hide columns based on cell value

Hi folks, my problem seems simple but I'm having a hard time googling it.

I have a sheet that has, say, 50 rows for employees in different locations. On the sheet is a cell containing a selection list of the different possible locations.

Now I would like to show and hide rows, based on what value is selected in my selection list. I have managed to apply conditional formatting this way, and it works. So, example:

Selection List is in C2 and contains Berlin, Munich, Hamburg to select from. Employees start from say row 10. I select (by mouse) rows 10,11,15,21 for four employees in Berlin. I give these rows conditional formatting by doing

format -> conditional formatting -> condition -> formula is -> \$C2="Berlin" -> apply style -> bold text.

Instead of bold text I'd like them to just get hidden. Afaics there's no way doing it via the "styles and formatting" method? How can I apply this condition and have rows shown or hidden? So, what I want is:

If C2 is "Berlin", then Show/Hide Rows XX, NN, YY or hide this row if C2 is "Berlin" applied to the lines. Lines need not be determined by some value they contain (although that would be nice, too), my data is manageable enough for me to select the lines concerned manually.

Cheers, Stefan

Libreoffice Calc 5.2.3.3

edit retag close merge delete

Sort by » oldest newest most voted

It is hard for me to detemine what you actually want/need.

Lines need not be determined by some value they contain (although that would be nice, too), my data is manageable enough for me to select the lines concerned manually.

is somehow irritationg for me.

First of all: You are actually talking of rows? (...not of columns as your subject suggested.)

I would read the explanations as if a filter, in specific the Aotofilter tool might be appropriate for the task. Anyway I would dedicate one column for a marker (whether entered directly or calculated) deciding positive/negative about visibility. This established I can use the AutoFilter, a variant filter, a selection by formulae ... or hiding/showing by user code. There may also be cases where hiding the content instead of the row itslef is appropriate ...

more

This is indeed not possible using the 'conditional formatting' technique, AFAIK.

Hiding a row is possible using a macro. The fundamentals are invoking the Row object and setting the IsVisible property to False. This can be done whenever the String in a desired cell has a certain value, and you could iterate over all the cells and you could pick the desired event to trigger the macro.

The very simple barebones example below will examine the contents of Cell A1 in the first sheet in the workbook, and if that cell contains the text Hello, then the macro will hide the row. If, on the other hand, it does not say Hello it will unhide the row.

Sub MyDoLoop

Dim oCell as Object, oSheet as Variant, oSheets as Variant, oString as String

oSheets = ThisComponent.getSheets(0)
oSheet = oSheets.getByIndex(0)
oRows = oSheet.Rows
oRow = oRows.GetByIndex(0)

oCell = oSheet.GetCellByPosition(0, 0)
oString = oCell.getString()
If oString = "Hello" Then
oRow.IsVisible = False
Else
oRow.IsVisible = True
End If

End Sub


You will need to scaffold code around this to trigger it at the right time, to iterate across the cells with the key values, etc. This is simply the core method. Hope this helps.

more

## Stats

Asked: 2016-12-21 13:40:40 +0200

Seen: 4,019 times

Last updated: Dec 29 '16