Forum Discussion
Use formula to define cell address in reference
SergeiBaklan Thank You, as a standalone cell it worked just right. But in my case I am using option 'linked picture' to get the value of cell together with formatting. When I am trying to paste the same formula into the 'linked picture' I get an error:
'This formula is missing a range reference or a defined name'So to sum it up, this is working perfectly in normal cell (don't mind the long substitute formula I can't do any better then that atm.):
=INDIRECT("'Risk Analysis - Matrix'!" & (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E10,"1","C"),"2","D"),"3","E"),"4","F"),"5","G"))&(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F10,"1","11"),"2","12"),"3","13"),"4","14"),"5","15")))And when I paste it into 'linked picture':
Thank You for your time 🙂
Another variant is to use formula for any cell in the workbook, as variant under the shape in E3, and shape itself link as =E3
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.
So create a Defined Name within Name Manager (Formulas tab), viz:
Name: MyLink
Refers to: =INDIRECT("'Risk Analysis - Matrix'!" & (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E10,"1","C"),"2","D"),"3","E"),"4","F"),"5","G"))&(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F10,"1","11"),"2","12"),"3","13"),"4","14"),"5","15")))
And then use
=MyLink
within the linked picture.
N.B. Do the values in E10 and F10 which are being referenced in your formula both contain integers varying from 1 to 5? If so, you can shorten considerably your current construction, not to mention make it non-volatile:
=INDEX('Risk Analysis - Matrix'!C11:G15,F10,E10)
Regards
