Forum Discussion
Generating an Formula using concatenation in excel
Hi There,
I am planning to generate a formula using concatenation operation in excel and it actually works but the only issue is it’s not executing it just gives the generated formula as output in the cell instead it should run that formula. Is there any function that would help me to execute that formula?
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.
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.
Let's say your formula is assembled as a text string in cell E2.
Select the cell to the right, i.e. F2.
On the Formulas tab of the ribbon, click Define Name.
In the Name box, enter Eval
In the 'Refers to' box, enter =EVALUATE(E2)
Click OK.
Still in cell F2, enter the formula =Eval
You can use =Eval in other cells directly to the right of a cell with a formula-as-text.
HansVogelaar Thanks for the Input Hans… I tried adding =Evaluate(formula) but it didn’t work because excel doesn’t have the evaluate function. Is there any way that I can give you the sample excel for this formula or somewhere we can talk about it?
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.
As variant with OfficeScript
function main(workbook: ExcelScript.Workbook) { const cell = workbook .getActiveWorksheet() .getRange("string") cell .setFormula( cell.getValue().toString() ) }Looks pretty understandable. Since I do not have access to Office Script, I tried typing into a Script Lab code window but it showed ExcelScript as an unknown namespace. 😞
ScriptLab has bit different interface. Not sure how exactly to run above in it, never used ScriptLab, only read about.
Know no reasons why Microsoft doesn't make OfficeScript available for consumers, at least on some subscriptions.
- Hi Sergei, Thanks for the Input, but this is not a static cell where I am trying use this formula this formula is going to be used for around 120+ columns to do. XLOOKUP based on the column header. Which basically reorders my columns from the server to the order which my team needs. Can you help me how this can be made possible with the VBA?
"...but this is not a static cell where I am trying use this formula this formula is going to be used for around 120+ columns to do..."
In general that's doable with OfficeScript, the only exact logic to be defined from which cells to take texts of formulae and to which cell to populate formulae itself.
Above sample was only to illustrate an idea.
