Ask Your Question

calc: show or hide columns based on cell value

asked 2016-12-21 13:40:40 +0100

modern_man gravatar image

updated 2016-12-21 13:42:54 +0100

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

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2016-12-29 19:04:27 +0100

Lupp gravatar image

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 ...

edit flag offensive delete link more

answered 2016-12-29 18:10:05 +0100

doug gravatar image

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
  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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 6,046 times

Last updated: Dec 29 '16