Forum Discussion

Krzysztof_Pe's avatar
Krzysztof_Pe
Copper Contributor
May 23, 2020

Use formula to define cell address in reference

Hi, As a user that is not so experience with excel I have a fairly simple question, but I am afraid that answers is not going to belong to the same kind. My intentions are to reference a cell in a ...
excel
Formulas and Functions
Krzysztof_Pe's avatar
Krzysztof_Pe
Copper Contributor
May 24, 2020

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 🙂

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 24, 2020

Krzysztof_Pe 

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

  • Krzysztof_Pe's avatar
    Krzysztof_Pe
    Copper Contributor
    May 24, 2020

    SergeiBaklan,

    @Jos_Woolley

    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 🙂

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      May 24, 2020

      Krzysztof_Pe 

      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.

      • Krzysztof_Pe's avatar
        Krzysztof_Pe
        Copper Contributor
        May 24, 2020

        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.

Resources