Formula works in cells, but not in Conditional Formatting

0
votes

I have written the following formula:

=IF(COUNTA(OFFSET(E5,0,0,1,(COLUMN($K5)-COLUMN(E5)+1)))=0,FALSE,TRUE)

e5 - beginning of range, k5 - end of range

It basically colors all cells in the given range (row) until it finds the last non-empty cell.

At least it was supposed to. The problem is - it works as a cell formula, but fails completely when applied as conditional formatting. The formula stays the same.

Example -Screenshot

1
excelconditional-formatting
Unrelated, but a formula in the form of =IF(condition, FALSE, TRUE) can be rewritten as =NOT(condition). – jsheeran
Yes, the previous version looked like that, but in this way it's a little bit more readable. For me at least... – PsyduckDebugging
@Albert: it is actually not more readable. It is more confusing, and it is slower to calculate. You should use something like: =COUNTA(...)<>0 or even: =0<>COUNTA(...) – virolino
@Albert: the image in the provided screenshot does not show the column names and the row numbers. That would be helpful. – virolino
@Albert: until it finds the last non-empty cell - is it in the true / false line, or in the colored line? – virolino

1 Answers

-1
votes

So there is a working solution to the issue. https://www.reddit.com/r/excel/comments/cf44pu/formula_works_in_cells_but_not_in_conditional/

basically all it takes is changing the original formula to: =COUNTA(OFFSET(E5,0,0,1,COLUMN($K5)-COLUMN(E5)+1))<>0 Can anyone explain why this version works?