Forum Discussion
Use formula to define cell address in reference
Unfortunately none of the above solutions work 😞
It always comes down to:
'This formula is missing a range reference or a defined name'It just seems like 'Linked Picture' doesn't like any kind of formulas inside its code.
This is very smart:
=INDEX('Risk Analysis - Matrix'!C11:G15,F10,E10) Thank You for that, it will definitely come in handy, but it gives wrong values tho.
If I feed it with numbers '2' and '5' it gives me 20 which is wrong since the outcome is always multiplication of 2 digits provided (But color is not always the same for 2 equal outcomes).
Upon linking it as SergeiBaklan mentioned the 'Linked Picture', since it is linked to cell 'under it', is taking its formatting instead of formatting of the cell that is calculated by it.
Anyway, I figured that doing formatting manually will be just easier and faster in this case at least.
But in case any of You still want to try and crack it for me or for yourselves I will attach excel file to this post. In short the table in sheet 'Scheme 1 - Risk Register' is taking 2 variables under consideration to assume risk level of activity. On first sheet there is a 5x5 matrix that defines the combination of the 2 variables.
Thank You for your time guys, I appreciate quick help a lot and a nice welcome 🙂
You may definitely simplify risk level formula as
=INDEX('Risk Analysis - Matrix'!$C$11:$G$15,$F10,$E10)
but I didn't catch what is the logic for the color of cells (in your sample it's not the same as in risk matrix) and what is the logic for the pictures in next column, what shall they show.
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?
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.