Forum Discussion

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

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 🙂

Jos_Woolley

Iron Contributor

May 24, 2020

Krzysztof_Pe

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