Forum Discussion
Conditional formatting with 3 color scale. Copy format to other cells
Sorry, I didn't test properly. It works differently compare to couple of years ago. Workaround could be
- apply the rule to $A$2 only
- using format painter apply to other cells but A3
- in CF manager duplicate rule for A2 and apply it to A3
Assume all cells have absolute references.
- File--> Options --> Formulas --> Working with Formulas --> R1C1 Reference Style - tick the box – Ok – back to the sheet. Now they are relative reference values.
- Select the cells/range --> Copy with Ctrl + C
- Go to the beginning cell of the desired cell/range where to paste the copied cell/s - Ctrl + V to paste.
- Go to File--> Options --> Formulas --> Working with Formulas --> R1C1 Reference Style - tick the box out – Ok – back to the sheet.
- After that, all should be absolute reference values again, with the application referring to the new cells.
That's what I have in mind...if it doesn't work for you, I recommend that you send the file (without sensitive data) or as a private message (if you think you absolutely have to) to me in order to to see how the file is structured.
Otherwise you and I will have to spend far too much time, which shouldn't be the case :).
Sorry didn't notice that you use a color scale.
You cannot use relative references in conditional formatted criteria for color scales, data bars, or icon sets.
You can change "Applies to" with R1C1 but no references in the reference cells.
This is only possible if you don't use color scales, data bars or icon sets in conditional formatting.
For your project, I can blindly agree with Mr. Sergei Baklan’s proposed solution :))
Attached are Mr. Sergei Baklan’s formulas, translated into German.
Add these as instructed by Mr. Baklan.
Minimum
=BEREICH.VERSCHIEBEN($C$2; ZEILE()-ZEILE($A$1)-1; 0)
Mittelpunkt
=(BEREICH.VERSCHIEBEN($B$2; ZEILE()-ZEILE($A$1)-1; 0)+BEREICH.VERSCHIEBEN($C$2; ZEILE()-ZEILE($A$1)-1; 0))/2
Maximum
=BEREICH.VERSCHIEBEN($B$2; ZEILE()-ZEILE($A$1)-1; 0)
Thank you for your understanding and patience
- Thanks for providing me with the German translation!
But I still have the problem that the formula refers to the 1st min and max.
