Google Sheets help please - conditional formatting

[audi321]

Hi all, I have a conditional formatting set up with a formula so that if the cell contains less than a sum of 4 other cells it turns green, if greater than, it turns red.

How do I copy this over to other cells down the column? I'm using format painter, but it doesn't change/carry the formula cells along. Can I not do this? Nightmare if it can't as there's hundreds of lines.

Thanks for any help.

[bigandclever]

Select all the cells first and then apply conditional formatting once. Isn’t it?

[audi321]

But how do I do the formula?

For example - cell A5 turns red if the value in it is more than the value of A1+A2+A3+A4

How do I do this if I'm selecting all the lines at once?

[bigandclever]

Is it always 4 cells then the total and the conditional format is done only on the total? So the next range would A6+A7+A8+A9 and A10 is the sum of those, so the same rule would be applied to A10 as it is for A5?

[audi321]

No, the lines are the same but the columns change.

Ie A5 turns red if value is greater than sum of A1+A2+A3+A4.

Then the next column is B5 turns red if greater than the sum of B1+B2+B3+B4 etc etc

Then C etc etc etc

Edited by audi321 on Tuesday 11th January 20:54

[mmm-five]

Has the conditional formula got a $ before the row reference?

If so, try removing it so that the row reference can increment when you copy the format down.

[bigandclever]

A screengrab of what the data looks like would help me, at least, because I can't even picture what you're attempting. How can B5 be "the next line"? It's the same line as A5

[audi321]

bigandclever said:

A screengrab of what the data looks like would help me, at least, because I can't even picture what you're attempting. How can B5 be "the next line"? It's the same line as A5

Sorry, I mixed columns and lines on my previous post (now fixed). Screen shot below of a similar example. Obviously I have manually done the 3 cells individually to make it work, but I have hundreds of columns in the real spreadsheet.

Edited by audi321 on Tuesday 11th January 21:05

[audi321]

mmm-five said:

Has the conditional formula got a $ before the row reference?

If so, try removing it so that the row reference can increment when you copy the format down.

No it hasn't a $. It seems to be a limitation of Google Sheets (and maybe excel?)

[boiler2003]

If there are no $ in the formula, Google Sheets (and Excel) use relative references which means that the formulas will be adjusted as you move across the columns.

Apply to range:
A5:Z5 (or whatever column you want to go up to)

Format rules
Greater than
sum(A1:A4)

Or A1+A2+A3+A4 would work just as well in the format rules.

The formula in the Format rules applies to the first cell in the range.

The $ make it an explicit reference which doesn't change, and can be placed in front of column, row, or both to fix either the column, row or both, but that is not necessary in this case.

[bigandclever]

I feel a bit dirty suggesting this, but at least you'd get something in about a minute.

Could you create an extra cell A6 as =sum(A5-sum(A1:A4)) and swipe that across to your end column, so you get to Z6 (or whatever) defined as =sum(z5-sum(z1:z4)).

Then apply Conditional Formatting as
Apply to range
a6:z6
Format Cells if..
Greater than
0
Custom
RED

And then change the size of row 6 to effectively hide the text but retain the colour.

So you get something like

[audi321]

bigandclever said:

I feel a bit dirty suggesting this, but at least you'd get something in about a minute.

Could you create an extra cell A6 as =sum(A5-sum(A1:A4)) and swipe that across to your end column, so you get to Z6 (or whatever) defined as =sum(z5-sum(z1:z4)).

Then apply Conditional Formatting as
Apply to range
a6:z6
Format Cells if..
Greater than
0
Custom
RED

And then change the size of row 6 to effectively hide the text but retain the colour.

So you get something like

Wow - a proper dirty workaround but yeah that works!! Cheers. So it does seem there’s a limitation around conditional formatting when using formula as the ‘condition’.

[boiler2003]

You shouldn't need to do anything dirty - screenshot below using the test data and no manual formatting honest!

[bigandclever]

boiler2003 said:

You shouldn't need to do anything dirty - screenshot below using the test data and no manual formatting honest!

[

It’s not answering the question either

[boiler2003]

bigandclever said:

It’s not answering the question either

What part of the question is it not answering?

[audi321]

boiler2003 said:

You shouldn't need to do anything dirty - screenshot below using the test data and no manual formatting honest!

Oh yes.......I thought I'd tried that and it didn't work. So even though it just says A1:A4, it is shifting the formula behind the scenes!

[bigandclever]

boiler2003 said:

bigandclever said:

It’s not answering the question either

What part of the question is it not answering?

I shall, of course, eat humble pie. But when I tried that, the condition was always summing the A column, not dynamically changing the columns. So each cell in row 6 was always comparing to the sum of a1:a4. Will have another go tomorrow!