Ask Your Question

Formula in Conditional formatting (v4.0.0.3) for large areas [closed]

asked 2013-02-09 22:53:15 +0200

Robert Popa gravatar image

updated 2015-10-21 15:42:34 +0200

Alex Kemp gravatar image

We used to work a lot with conditional formatting. After updating to the following was not working anymore:

Rows from 2 to 39 are completed with data collected from real life. In row 40 we have a model on each column. data is collected for multiple criterias (Ak2:CA39). We check what we find on the field with the model and mark with style RED those entries that are not matching the model.

For this we setup the Formula =if(isempty($a2),0,if(ak2=ak$40,0,1)) in the Conditional Formatting of AK2 for example. Afterwords we copy the conditional formatting to the rest of the cells, Ak2:CA39.

But now it doesn't work at all. Not even for one cell!!!

Any ideas? How can you do it?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-21 15:43:08.108710

2 Answers

Sort by » oldest newest most voted

answered 2013-02-10 00:46:08 +0200

m.a.riosv gravatar image

Try selecting first the range where you want to use the formula for the conditional format, and then go to the conditional format.

Take a look at the open bugs about copy cells with conditional formatting

edit flag offensive delete link more

answered 2013-02-10 07:29:26 +0200

Robert Popa gravatar image

It seams that if I use the conditional formatting for just one cell, the formula works =if(ISBLANK($a2),0,if(ak2=ak$40,0,1))

The only problem was when it was pasted special to other cells. It is at this moment a bug and it is worked upon.

However, there is a work around:

  1. Make the first cell with the right conditional formatting formula
  2. FILL DOWN/RIGHT the cell (NOT copy/paste special)
  3. Enter the data into the cells
  4. and it works...

I hope the bug is going to be resolved. As i saw in the description it behaves much worse then this.

edit flag offensive delete link more

Question Tools


Asked: 2013-02-09 22:53:15 +0200

Seen: 1,097 times

Last updated: Feb 10 '13