Use formula to define cell address in reference

Copper Contributor

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 table to a cell in other sheet. But the referenced on other sheet has to be dynamic, in a way that formula will "calculate" address of the cell based on inputs in 2 different cells.

Example:

 

='Scheme 1 - Risk Register'!FORMULA THAT GIVES CELL ADDRESS IN TEXT(SUCH AS D12)

 

 My problem is, that I cannot make reference and formula work together. Is there a special syntax or signs to make it work?

12 Replies

@Krzysztof_Pe 

That could be

=INDIRECT("'Scheme 1 - Risk Register'!" & D12)

@Sergei Baklan 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':

Krzysztof_Pe_0-1590316739799.png

Thank You for your time :)

@Krzysztof_Pe 

 

So create a Defined Name within Name Manager (Formulas tab), viz:

 

NameMyLink

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

@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

@Sergei Baklan,

@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).

Matrix.png

 

Upon linking it as @Sergei Baklan 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.

Risk Analysis.png

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 :)

@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.

@Sergei Baklan 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.

@Krzysztof_Pe 

If color is from matrix, consequence=5 and likelihood=2 shall have red color, correct?

@Sergei Baklan 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.

@Krzysztof_Pe 

When we may apply conditional formatting rules as

image.png

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.

@Sergei Baklan 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 :)