Formula required referencing a cell's formula

[hsandeep]

My Workbook contains 2 worksheets: Sheet1 & Sheet2

In Sheet2: B2=Sheet1!T17

In C2 I want to get value of column A i.e. 'like' =Sheet1!A17...but I don't want to punch this formula. I need a reference formula with respect to cell B2.

Thanks in adv.

 

[steve the fish]

Like this??

=INDIRECT(B2)

 

[hsandeep]

Like this??

=INDIRECT(B2)

Seems YES..but how to get =Sheet1!A17 ?

 

[steve the fish]

That's what you have essentially.

 

[hsandeep]

Could not get....
In Sheet2: B2=Sheet1!T17
In C2 I want A17 of Sheet1

 

[steve the fish]

Ok try:

=INDIRECT(SUBSTITUTE(B2,"!T","!A"))

 

[hsandeep]

Ok try:

=INDIRECT(SUBSTITUTE(B2,"!T","!A"))

I am getting #REF!

 

[steve the fish]

If your cell B2 is an actual working formula then I think you need VBA. I thought it contained the text Sheet1!T17 not including the =. I certainly wouldn't know another way other than VBA.

 

[hsandeep]

Yes The cell B2 has an actual working formula

 

[Zakkaroo]

I've borrowed from the thread linked below - the consensus in that thread seemed to be the best way was a VBA function

http://www.mrexcel.com/forum/excel-questions/33170-code-extract-formula-cell.html

Here is a custom function that will return a cell's formula:

Function CellFormula(Rng As Range) As String
    CellFormula = Rng.Formula
End Function

Paste the code into a General module.

Do the above, then:

C2 =INDIRECT(RIGHT(SUBSTITUTE(cellformula(B2),"!T","!A"),LEN(SUBSTITUTE(cellformula(B2),"!T","!A"))-1))