Conditional Formatting with formula wont apply styles across multiple rows

2
votes

I read loads of answers here, but none of them applies to my issue I think.

I need to have a coloured column based on a dropdown list value. If it's Not Paid, the A column has to be red if it's Paid, it has to be green.

When I apply =K12="Not Paid" formula, styles apply only for only one cell. if I shoose apply to all empty cells it works as expected. but as soon as I use any formula styles are all over the place.

Any ideas?

Here is an example of the spreadsheet I am working with https://docs.google.com/spreadsheets/d/18Y-2jeKue9aH4gGdKHZjECygo47JFOp2W11C2sy7fJk/edit#gid=553917738

2
google-sheetsgoogle-sheets-formulags-conditional-formatting

2 Answers

1
votes

This is due to the conditional formatting via formula that can be a little tricky. The solution you need is:

=K$12="Not Paid"

This is because when you use =K12="Not Paid" it uses relative references. That means that if you apply this conditional formula in a range like "A12:A2", for cell "A12" the formula will be exactly as you wrote, but for cell "A13" it will have a formula like =K13="Not Paid", which is not true. I hope you can take a look on this more in depth tutorial.

2
votes

all you need is to extend the range to K12:K like:

0

UPDATE:

let R10 be:

=ARRAYFORMULA({K12; IF(IF(LEN(B11:B&D11:D&K11:K), VLOOKUP(ROW(A11:A), IF({IFERROR(REGEXEXTRACT(K12:K,
 "Undefined|Quote|Accepted|In-progress|Cancelled|On-hold|Not Paid|Paid 50%|Closed"));""}<>"", {row(A11:A), {IFERROR(REGEXEXTRACT(K12:K,
 "Undefined|Quote|Accepted|In-progress|Cancelled|On-hold|Not Paid|Paid 50%|Closed"));""}}), 2, 1), )<>"",   IF(LEN(B11:B&D11:D&K11:K), VLOOKUP(ROW(A11:A), IF({IFERROR(REGEXEXTRACT(K12:K,
 "Undefined|Quote|Accepted|In-progress|Cancelled|On-hold|Not Paid|Paid 50%|Closed"));""}<>"", {ROW(A11:A), {IFERROR(REGEXEXTRACT(K12:K,
 "Undefined|Quote|Accepted|In-progress|Cancelled|On-hold|Not Paid|Paid 50%|Closed"));""}}), 2, 1), ), QUERY(IF(LEN(B11:B&D11:D&K11:K), VLOOKUP(ROW(A11:A), IF({IFERROR(REGEXEXTRACT(K12:K,
 "Undefined|Quote|Accepted|In-progress|Cancelled|On-hold|Not Paid|Paid 50%|Closed"));""}<>"", {ROW(A11:A), {IFERROR(REGEXEXTRACT(K12:K,
 "Undefined|Quote|Accepted|In-progress|Cancelled|On-hold|Not Paid|Paid 50%|Closed"));""}}), 2, 1), ), "offset 1", 0))})

0

and S9 be:

=ARRAYFORMULA({"";IF(INDIRECT("R9:R"&ROWS(A9:A)-1)="", 1, )})

0

then red color will be:

=($R11="Not Paid")*($S11<>1)

0

and green color will be:

=($R11="Closed")*($S11<>1)

0