Return formula as text

[Guest]

Hi,

I have a load of cells which contain formula links to other tabs i.e.

='sometab'!A34

and of course they return the value of the cell. Further down I'm writing a
new formula and I don't want to re-write all the formulas, I would prefer to
return the tab as text a then create new references.

return the formula from above and cut it so it reads

'sometab'!

Then use indirect function to put the tab reference into the new forumlae.

Anyone know a walk around to return the formula NOT the value?
Cheers,
Paul.

 

[Guest]

first enter this one-line UDF:

Function formuli(r As Range) As String
formuli = r.Formula
End Function

then if A1 contains:

=sometab!A34

=MID(formuli(A1),2,FIND("!",formuli(A1),1)-1)
will return:
sometab!