CODEWITHSUNDEEP
excelexcel-formula
bb_pt
bb_pt

Reputation: 46

Evaluate text containing a named range

How do I evaluate a text string that contains a named range? I tried with EVAL, EVALUATE and INDIRECT with no success.

A bit more..

For another system, I've got 50+ formulas with 200+ variables, an example follows: 

<ABC>+<DEF>/<TRE-1>

To be able to use them all at once in Excel without manually changing every formula, variable and operator, I use a couple (or more) SUBSTITUTE formulas to render a string that Excel might be able to digest:

=ABC+DEF/TRE_1

I referenced all variables to named ranges. For e.g.:

ABC is cell B2, value 5.4

DEF is cell B3, value 3.2

TRE_1 is cell B4, value 1

But then the I can't get the resulting string evaluated with INDIRECT or EVALUATE. It just gives me a #NAME or #REF error, because it seems it doesn't recognize the variable as a named range (and thus a value).

Any ideas?

I don't want to implement this in VBA. I know it's possible using the .RefersTo method..

Upvotes: 3

Views: 4023

Answers (3)

bb_pt
bb_pt

Reputation: 46

The VBA solution (as seen here or here) is to setup an UDF(User Defined Function) inside a module in the current workbook as for e.g.:

Function Eval(ByVal S As String) As String

Eval = Evaluate(S)

End Function

Having the variables setup as named ranges, in a cell enter:

 =Eval("ABC+DEF/TRE_1")

And it will pop-out the result correctly.

Now.. I don't want to use VBA...

Upvotes: 0

Peter L.
Peter L.

Reputation: 7304

Try to use this syntax:

=INDIRECT("ABC")+INDIRECT("DEF")/INDIRECT("TRE_1")

Pay attention to quotes. It seems that's how INDIRECT works: =INDIRECT("ABC+DEF") returns #REF!

See also this sample: https://www.dropbox.com/s/jxj7cgjmnx8iv0t/INDIRECTwithNamedRegions.xlsx

Upvotes: 4

Charles Williams
Charles Williams

Reputation: 23520

INDIRECT dows not evaluate formulas, only references: you need to use Evaluate, but thats only available via the XLM or COM interfaces.
You can embed Evaluate inside a defined name formula, but often this method is impractical.

Upvotes: 0

Related Questions