I’m trying to conditionally color the background of a cell based on whether or not other cells in the same row contain a value.
I can set the format for a single entry (e.g. for cell B14) as follows:
Select cell
Format/Conditional/Condition
Formula is
AND(NOT(ISBLANK(F14)), ISBLANK(G14))
Apply Style MyStyle
However, when I try to generalize the formula to propagate to other cells, it doesn’t work:
AND(NOT(ISBLANK(ADDRESS(f,ROW()))),ISBLANK(ADDRESS(g,ROW())))
Hints?
For B14 set Formula is…
AND(NOT(ISBLANK(B14)),NOT(IF(1=COUNTIF(14:14,"*"))))
This will set your desired style whenever
It seems to copy/paste/paint okay for me on quick tests.
That’s not what I want. I want Bn’s (cell B in any row) format based on Fn being non-empty and Gn being empty; and do it regardless of whether Bn has content or not.
As noted originally,
AND(NOT(ISBLANK(F14)), ISBLANK(G14))
works for a specific cell in a specific row (B14); I want an expression that will work for Bn (i.e. cell B in any row). So the row number cannot be explicitly enumerated.
Hmmmm.
Practically speaking, simple addresses in conditional formatting are just relative offsets based on the address of the selected cell at time of entry, or basically the top left cell of a single conditionally formatted region.
Do you mean to always check F and G, or do you mean to check 4 and 5 cells to the right of the conditionally formatted cell?
If you just mean 4 and 5 cells to the right–for example if you are just meaning to copy the formatting down the B column–then LO Calc will generalize the formula for you and your formula should work fine. Just copy or paint down the formatting. Note: you may need to do a recalculate (ctrl+shift+F9) before you will see the correct formatting after expanding your conditionally formatted region using a fill or copy operation. [I can’t tell you exactly what situations lead to this, but it does show up as a bit of a glitch at times.]
I mean for Bn, always check Fn and Gn.
How do I paint-copy only the conditional formatting for cells? If I select B14 (which has the proper conditional formatting), is there some ctrl-shift-meta combination that I can drag down with?
But I am pretty confused:
I have discovered I can get what I want by:
select B14 (which has the correct conditional formatting)
click format/conditional/manage...
select the range containing the formatting I want
click edit
extend the range to include additional B cells
But that seems rather awkward.
When I do the above, I get what I want for a range of B cells, but the conditional format itself still refers only to F14 and G14; it doesn’t have an expression referencing the current row. How does LO know to use Fn and Gn for the format of Bn, when the expression uses F14 and G14?
See @eeigor for further explanation about:
In Excel (or really, LO in general context) a reference to B2 within the cell A1 just means over one and down one. It’s really just “code” for B=2 - A=1 which is 1 and 2 - 1 which is 1. In Conditional Formatting the same applies, but it is always as if the top left cell of the conditional formatting range is a “stand in” for whatever cell is being checked. So in the case of A1 referencing B2, every cell will check over 1 and down 1.
As for copying the format:
The formula is correct, but column references may be made relative if you want to freeze the columns:
Range: B14:B16
Formula is AND(NOT(ISBLANK($F14));ISBLANK($G14))
Do for one row, then copy the format of the cell in it (column B) using a brush, and extend to other cells in column B. Ideally, the range should be continuous (eg., not <E14;E15;E16> for 3 cells), but when formatting multiple ranges without gaps, it will.
This is nonsense. Everything is calculated without your participation. No ROW functions needed.
The problem is that you don’t understand the meaning of relative references.
F14 means that it is in the current row of the current column, that is, RC, not R14C6 in Excel notation as you think. Do you understand the difference?
A relative reference is made to the first cell (cells in a row) of the range, and then it becomes the source position for calculating other references (offsets).
For example, for B15 it would be F15, that is, R[1]C, where 1 in brackets means an offset of 1 cell down from the source position.
Switch to Excel R1C1 formula syntax (grammar), and everything becomes clear.
Ok, thanks, I follow you. However, what if I want a formula I can copy and paste anywhere, without regard to the original row? In that case I need a way to refer to the current row. In other words, I want an expression I can paste into ANY cell in a row (other than F and G), to express the condition “column F is not empty AND column G is empty”.
After some head pounding, it appears that the following works:
AND( NOT(ISBLANK(INDIRECT(ADDRESS(ROW(),6)))), ISBLANK(INDIRECT(ADDRESS(ROW(),7))))
Is there a more compact way of expressing this?
You just have to refer to the source position, as I have shown.
Your method was correct:
AND(NOT(ISBLANK(F14));ISBLANK(G14))
if row 14 is the upper row of the range and F & G are the left columns of that range. Something like this…
CFormat.ods (11.7 KB)
It’s your way. And that it true.
Have you done it?
No F & G and others. See:
You will have to learn to understand the Calc A1 style in relative address mode.
Thanks for your patience. To simplify things, lets forget about conditional references. I’ve attached a sheet with 4 cases, and a simple expression in each of the first two cells: =ISBLANK(RCn), where n is either 3 or 4. It’s my understanding this is an absolute reference to the current row, cell n. But in all cases the expression returns TRUE. Obviously I still don’t get it.
CellRefs_1.ods (9.0 KB)
My error, the refs in that sheet should be something like
=ISBLANK($D1)
where $D means absolute column D, and 1 means the next row?
But then how do I reference the current row? $D0 doesn’t work, nor does simply $D.
Edit: I think I see now…
A row number is always an absolute row number, but without the $ it really means “subtract the row number in the formula from the row number where the formula originated to get an offset, then add the destination row number”. So the number in the formula is always an actual row number, which without the $ is used to compute a new row number whenever the formula is copied.
For example, the entry $D3 in row 7 means the row offset is (7-3) = 4, and when pasted into row 12 would become (12-4)=8, and the formula in row 12 would appear as $D8
File
CellRefs_1 (2).ods (16.3 KB)
And R1C1 style should be turned on and all formulas will change by themselves, and you in Calc A1 mode started entering references like RC…
And the cells to be checked are 3 and 4 columns apart from any cell (wherever it is) to which the conditional format applies.
Thanks both for your patience and explanations.
