Referencing a formula (as text)

[Nelson]

Hi, can anyone help with the following?

I want to pick up a formula (written as text) from one cell so it can be
used in another function? This will then be applied to a range of cells by
filling-down.

For example if I write a function as VLOOKUP(X,Table,3,false) without the =
sign and therefore store it in a cell as text, I want to be able to
reference that cell and apply the function.

Is this possible?

Nelson

 

[Guest]

You'd need a macro to do that. If that formula were in D3, for example, this
line of VBA would do it:
Answer = evaluate("=" & [d3])

Bob Umlas

 

[Dave Peterson]

Just to add to Bob's response....

You'd need a User Defined Function.

This one worked ok for me:

Option Explicit
Function Eval(str As String) As Variant
With Application
.Volatile
Eval = .Evaluate(str)
End With
End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=eval(a1)

Where A1 contains your formula/string.

 

[Nelson]

Thanks everyone,

Will look at David McRitches site.

Nelson