Forum Discussion
Generating an Formula using concatenation in excel
This uses the XLM command EVALUATE within Name Manager where it works correctly just as HansVogelaar 's solution. With 365 the process can be taken a step further and the name can be a Lambda function.
EVALUATEλ = LAMBDA(formula, EVALUATE(formula))The sole advantage is that EVALUATEλ can be reused with multiple strings.
See the attached workbook. It uses PeterBartholomew1's elegant Lambda wrapper of EVALUATE.
Save the workbook to a Trusted Location for Excel. It is now a .xlsm workbook since the use of EVALUATE requires it.
Do I need to enable anything for this formula to work I see it says #BLOCKED
After downloading the workbook, right-click the file in File Explorer.
Do you see a link or button "Unblock" ?If so, click it.
And, as mentioned before, make sure that the folder containing the workbook is a trusted location for Excel (File > Options > Trust Center > Trust Center Settings... > Trusted Locations)
- Hey Yes Hans, I can see that formula is now working in my Sample report... How to add the same function to my original one? Is this any macro kind of thing that I need to add? Sorry I am not used to Macros or XMML scripts Please help me out.
On the Formulas tab of the ribbon, click Name Manager.
You'll see Evaluateλ and its definition - I copied it from PeterBartholomew1's reply.
You can create the same definition in your workbook - click Define Name... on the Formulas tab of the ribbon. You can copy/paste both the name and what it refers to.
I added a missing comma to your formula, and wrapped it in Evaluateλ.
You can do the same in your workbook.