calc: show or hide columns based on cell value

[modern_man]

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

[doug]

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.

[morphingstar]

Unfortunately OO and LO both are unable to fit column width according to a cell content. For this function you need ATARI-1000 of years 1980+. The problem is that LO+OO can not read a cell content and modify the spreadsheet display accordingly without storing the read number. With my last millennium computer I could create a macro which read cell content (number value) and have it act on cell width of the actual cell (column), then {right} and repeat. With OO+LO recorded macros the result of the cell reading is stored inside the macro. Result: You can use it once, any change in the cell containing the width number is ignored on next run. My comfort was I could select different rows to run the macro, adapting column width according to the row’s contents. Macro creation was easy, just record it. No programming needed. No hide / show needed, to hide put 0 into cell.
Of course if it is simple it is outdated, if it is modern it must be complicated or even impossible.

[Lupp]

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 …