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.
It does have the EVALUATE function since that pre-dates the introduction of VBA to Office. However, it does not run on a standard worksheet, hence the way it is wrapped within a Name using Name Manager. It may work on a Macro sheet but that is outside my area of knowledge.
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)
