• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multi-Level Cell Referencing (row/column referencing)

D

davidlim

Member
hi,


i have a tricky question on cell referencing (something along INDIRECT or ADDRESS or even CELL).


example:


F12: aaaa

E14: 1111

J11: =F12 (value is aaaa)


K11: I want to get value 1111 at E14, but only from cell J11's cell value reference + and - columns & rows.


explanation:


basically, from J11, i have a formula that reference to column F and row 12.


I want to "go" to column F-1 and 12+2 (i.e. column F minus 1 column, and row 12 plus 2 rows down) ... end address is E and 14, so if i use =INDIRECT(E14), i shud get 1111.


hope this clears the intention :)


thanks!
 
Hi David ,


I am not sure I have understood you , but can you try this ?


=OFFSET(INDIRECT($J$11),2,-1)


This will work only if J11 contains the text F12 , and not the formula =F12.


Narayan
 
hi narayan,


i am actually targeting the address inside J11 ... which points to F12.


in a simpler term, it shud be =OFFSET(INDIRECT($F$12),2,-1) ... but this 'F12' is referenced from J11.
 
Hi David ,


The INDIRECT function expects a string parameter ; thus if you type in text in cell J11 , then F12 will be used as the address reference in the first parameter of the OFFSET function.


If you put in =F12 in cell J11 , then it becomes a formula , and a formula cannot access any other cell's formula ; Excel 2013 has a FORMULATEXT function , but I am not sure what this can do. For earlier versions , the only way to access a cell's formula , to my knowledge , is through VBA.


Narayan
 
thanks narayan for ur explanation.


am avoiding vba. FORMULATEXT is useful, but not available to Excel prior to 2013.


nvm, thanks again. ;)
 
You must log in or register to reply here.
Back
Top