Evaluate text containing a named range
How do I evaluate a text string containing a named range ? I have tried with EVAL, EVALUATION AND INDIRECT without success.
A bit more.
For another system I have 50+ formulas with 200+ variables, the following example:
<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 can digest:
=ABC+DEF/TRE_1
I have referenced all variables to named ranges. For example, for example:
ABC - cell B2, value 5.4
DEF - cell B3, value 3.2
TRE_1 - cell B4, value 1
But then I cannot get the resulting string obtained with INDIRECT or ESTIMATE. It just gives me a #NAME or #REF error because it seems like it doesn't recognize the variable as a named range (and therefore a value).
Any ideas?
I don't want to implement this in VBA. I know this is possible using the .RefersTo method.
source to share
Try this syntax:
=INDIRECT("ABC")+INDIRECT("DEF")/INDIRECT("TRE_1")
Pay attention to quotes. Seems to work INDIRECT
: =INDIRECT("ABC+DEF")
returns#REF!
See also this example: https://www.dropbox.com/s/jxj7cgjmnx8iv0t/INDIRECTwithNamedRegions.xlsx
source to share
The VBA solution (as you can see here or here ) is to set up a UDF (User Defined Function) inside a module in the current workbook, like for example:
Function Eval(ByVal S As String) As String
Eval = Evaluate(S)
End Function
After setting the variables as named ranges in the cell, enter:
=Eval("ABC+DEF/TRE_1")
And it will output the result correctly.
Now .. I don't want to use VBA ...
source to share