Reading formulae in a different cell

If cell A1 contains a formula e.g. =Vlookup(50,RANGE,2)=99.45 say, how can I
reference or find the 3rd argument in the formula from another cell. When I
use any formula that references A1 it always uses the result of the formula
(99.45 here) and not the text of the formula ( =vlookup(50.RANGE,2) ).

Thanks
--

Albert

Hi Albert,

You can use this User Defined Function (UDF):

Function ShowFormula(a As Range)
If Application.ReferenceStyle = xlR1C1 _
Then ShowFormula = a.FormulaR1C1 _
Else: ShowFormula = a.Formula
End Function


--
Kind regards,

Niek Otten

"Albert" <Albert@discussions.microsoft.com> wrote in message news:C358C6DE-59C5-431B-9466-0D85EBAB63A0@microsoft.com...
| If cell A1 contains a formula e.g. =Vlookup(50,RANGE,2)=99.45 say, how can I
| reference or find the 3rd argument in the formula from another cell. When I
| use any formula that references A1 it always uses the result of the formula
| (99.45 here) and not the text of the formula ( =vlookup(50.RANGE,2) ).
|
| Thanks
| --
|
| Albert

Many thanks for your help.
--
Albert


"Niek Otten" wrote:

> Hi Albert,
>
> You can use this User Defined Function (UDF):
>
> Function ShowFormula(a As Range)
> If Application.ReferenceStyle = xlR1C1 _
> Then ShowFormula = a.FormulaR1C1 _
> Else: ShowFormula = a.Formula
> End Function
>
>
> --
> Kind regards,
>
> Niek Otten
>
> "Albert" <Albert@discussions.microsoft.com> wrote in message news:C358C6DE-59C5-431B-9466-0D85EBAB63A0@microsoft.com...
> | If cell A1 contains a formula e.g. =Vlookup(50,RANGE,2)=99.45 say, how can I
> | reference or find the 3rd argument in the formula from another cell. When I
> | use any formula that references A1 it always uses the result of the formula
> | (99.45 here) and not the text of the formula ( =vlookup(50.RANGE,2) ).
> |
> | Thanks
> | --
> |
> | Albert
>
>
>

You could use a UDF to display the formula as a string and then parse the
string with MID(), LEFT(), etc.:

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



and use like =textofformula(A1)
--
Gary's Student


"Albert" wrote:

> If cell A1 contains a formula e.g. =Vlookup(50,RANGE,2)=99.45 say, how can I
> reference or find the 3rd argument in the formula from another cell. When I
> use any formula that references A1 it always uses the result of the formula
> (99.45 here) and not the text of the formula ( =vlookup(50.RANGE,2) ).
>
> Thanks
> --
>
> Albert