Forum Discussion
Use formula to define cell address in reference
SergeiBaklan I just showed the pictured next to the cell it takes information from, normally as you suggested it would be on top of it.
I did it to show that it takes format from the cell it refers to, so in this case for example 10 with green background instead of the calculated cell which is 10 with red background in the matrix.
To put it short I want the cells under 'Risk Level' Column to take Value and formatting from Matrix based on variables likelihood and consequences.
If color is from matrix, consequence=5 and likelihood=2 shall have red color, correct?
Krzysztof_Pe , glad to help
SergeiBaklan Yeah, that did the trick. The Matrix will always stay the same, so there is no problem with updating formatting. Thank You guys for your time 🙂
When we may apply conditional formatting rules as
Formulas for rules are
Green: =($E10<=2)*($F10<=2)+($E10=1)*($F10=3) Red: =($E10=5)+($E10=4)*($F10>2)+($E10=3)*($F10>3)+($E10=2)*($F10=5) Yellow (all the rest): =($E10>0)*($F10>0)*($E10<6)*($F10<6)Small minus is - if you change colors in matrix, rules formulas are to be updated for conditional formatting. But I guess the matrix is not so dynamic.
In general, it could be one set of formulas to color both matrix and column, or just create helper range which will indicate colors to apply by values.
SergeiBaklan Yes, this is correct, In the picture I sent I set it manually to green for you to see difference and from where the 'Linked Picture' takes information.