Forum Discussion
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 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
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
