How to get the applied conditional format of another cell?

English
#1

I have the following: conditional format “G2G” if a cell has the value “done”, scope is the entire column; this works as expected.
Above any cell containing “done” is always an empty cell, below are an unknown number (at least 1) of adjacent cells with a different text which can differ from cell to cell.
I want to copy the applied format to all adjacent non-emty cells below the one containing “done”, regardless of their content. This would improve the readability of the sheet greatly.

Is there a function that I can use to do this?
Is this even possible?

BTW: Version: 7.3.7.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 6; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 1:7.3.7-0ubuntu0.22.04.4
Calc: threaded

1 Like
#3

How do you give a name to a conditional format? I don’t know one.

#4

Thanks for your input.
Hmmm… :thinking:
The sequence you describe copies the format of the “done” cell to all the occupied cells below.
However, the scope of that format is already the entire column…
Moreover, the format itself is conditional and depends on the value of the cell.

If I extend the formula for the format to include the non-empty cell above It works for the first cell below the one with value “done”, the others fail because the cell above has another value than “done”…
I am looking for a way to get the actual applied format of a cell, so I can put a formula like OR(A2="done",cellstyle(A1)="G2G") as the condition, if there only was a function like that.

BTW: naming a conditional format is fairly simple: when assigning it, just choose “New Style” from the list.
Editing it afterwards seems to be impossible however (or that is a bug: I cannot change any format, not even the default).

#5

This is surely meant as a comment on the “suggested solution” below? Please post comments in a sequence allowing everybody to understand the context.

Unfortunately I seem to not understand.

What is this suppopsed to mean?

I’m not a developer and may misunderstand things myself, but imo:
CF never “acually applies a format to a cell”. It basically changes the appearance of the visual part of the screen. Funny effects produced by CF in “All Cells” mode e.g. don’t even exist as cell properties. And the CF itself is assigned to a CellRange in an abstract way.

#6

My bad: I’m not really used to that method. Sorry.
I just tried exactly that what you described: it does not work, the selected celld maintain their appearance. Which I sort of expect: their value does not meet the criteria of the “G2G” conditional format…

By “the format itself is conditional” I meant that it is a conditional format, sorry if that stirred a confusion.
Note my avoidance of the abbreviation CF, which I deam ambiguous: it can also mean Cell Format…

I was unaware how a conditional format actually does what it does, the term “format” and the way it is entered lead me to believe that it was an actual format that only overrides the assigned/default cell format if the condition is met.

Armed with this knowledge, the question changes: how do I get the applied (for lack of a better word) conditional format of a cell, if any? In other words: can one assess the state of the conditional format of a cell?

I prepared a small spreadsheet to demonstrate.
demo.ods (16.6 KB)

1 Like
#7

“Cell Format” isn’t an established term. (Use CellSytle or direct formatting instead.) ConditionalFormat is.
Obviously there are still misunderstandings I can’t safely tidy up. And I had misunderstood the original question.

As far as I can tell there is no way to get with a CF based on standard functions what you want. Still not being sure I understood your intentions fully, I wrote just for fun a UserDefinedFunction resorting a bit to VBA means for the purpose. You find it in the attached example. To get it working you need to set macro security to medium and to allow execution of the document macro on loading the example.

Pleas note: The “solution” is inefficient and very raw. I would strongly dissuade from using it.
disask100148veryStubbornCFusingUDF.ods (21.4 KB)

#8

imagen

So the problem is how to get the content of the upper cell of a group.

#9

That would work, it might even be possible to accomplish with standard functions…
Thanks for the suggestion!

#10

@ Lupp:

Thanks! That does exactly what I wanted.
But macro programming is beyond me: I do not comprehend how it works (which can pose a problem later on), so I take your advice and not use it…

#11

Did you find an efficient way to get “the group leader” by standard functions?

I only found rather ugly ways of doubtable efficiency. See attached:
disask100148verySpecialCFbasedOnStandardFunctions.ods (24.2 KB)

The usage of complicated CF formulas of the kind may also cause propblems for later development. They aren’t quite easy to understand in some cases. However, even if complicated they may be more efficient than then usage of a specialised UDF.

1 Like
#2
  • Select the bottom cell containing "done".
  • Ctrl+C to Copy the cell.
  • Shift+Ctrl+DownArrow to expand the selection to the non-empty adjacent cells below.
  • Shift+Ctrl+V to get the Paste Special dialog.
  • Enable the Formats option as the only one.
  • OK

You may also want to record this sequence as a “macro”. It should work.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.