I want to enter a formula in a cell as a string, and then reference that string into a formula.

Example: Cell A1 contains the text 'SUM(K2:K10)*5' (no calculation)

Cell B1=10 (A1)

Result B1=10 SUM(K2:K10)*5) and actually calculates.

Also needs to work if A1 is not a full formula, but I just want that string of text used in B1 as part of the formula as if it was naturally in B1.

So if A1 was just the string 'SU' and in B1 I put =A1&M(K2:K10) it would combine SU and M and calculate as a proper formula as =SUM(K2:K10)

Now I can change the text in A1 to mimic more complex formulas and B1 will act as if that text is truly a part of the formula and calculate. From what I read the function =EVALUATE does this, but it appears to not be in newer excel? I just keep getting error that function is not valid and not in function library..

Maybe a way to use Indirect or Concate to achieve this?

Thanks. 365 Excel 2020 64bit

CodePudding user response：

You can do it with help of Name Manager (VBA is an option but in the context of this question, I am ruling this out)

Formulas tab – Name Manager – New – Put any name in Name: box, say you put GetResult

Put following in Refer To: box

=EVALUATE($A$1)

Now in B1, just put following

=GetResult

Note - If you use this approach, then you will have to save your file as .xlsm.

CodePudding user response：

EVALUATE is and old Excel version 4 macro language function and is not directly available as a worksheet function. You can build a VBA UDF to get at the VBA EVALUATE method (which BTW has a lot of "strange behaviours". Or if you have LAMBDA you can build a named Lambda that calls Evaluate. Named Lambda called Evil with a formula =LAMBDA(x,EVALUATE(x))

then if your formula string is in cell Z99 you can call the Evil Lambda as =Evil(Z99)

But be aware that using XLM macro functions in this way is deprecated and can fall foul of anti-virus etc.