SOLVED

# Generating an Formula using concatenation in excel

Copper Contributor

# 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?

21 Replies

# Re: Generating an Formula using concatenation in excel

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.

best response confirmed by AjayGujay (Copper Contributor)
Solution

# Re: 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.

# Re: Generating an Formula using concatenation in excel

As variant with OfficeScript

``````function main(workbook: ExcelScript.Workbook) {

const cell = workbook
.getActiveWorksheet()
.getRange("string")

cell
.setFormula( cell.getValue().toString() )
}``````

# Re: Generating an Formula using concatenation in excel

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. :(

# Re: Generating an Formula using concatenation in excel

@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?

# Re: Generating an Formula using concatenation in excel

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.

# Re: Generating an Formula using concatenation in excel

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?

# Re: Generating an Formula using concatenation in excel

Thanks for putting your valuable time on this Peter I will check the work around provided and get back to you in a Private message as well.

# Re: Generating an Formula using concatenation in excel

See the attached workbook. It uses @Peter Bartholomew'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.

# Re: Generating an Formula using concatenation in excel

Do I need to enable anything for this formula to work I see it says #BLOCKED

# Re: Generating an Formula using concatenation in excel

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)

# Re: Generating an Formula using concatenation in excel

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.

# Re: Generating an Formula using concatenation in excel

On the Formulas tab of the ribbon, click Name Manager.

You'll see Evaluateλ and its definition - I copied it from @Peter Bartholomew'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.

# Re: Generating an Formula using concatenation in excel

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.

# Re: Generating an Formula using concatenation in excel

"...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.

# Re: Generating an Formula using concatenation in excel

One of the strengths of Excel is its universality. I have doubts about committing serious effort to getting up to speed on a topic if I cannot communicate it to others and, worse, I may in due course lose access to my own work!

# Re: Generating an Formula using concatenation in excel

Hey Guys Thank you Too Much for all your helps, its an Inside Formula error I was facing... I was able to re-correct it now and the Lambda formula is now working great and efficiently Thanks @hans @peter @Sergi

# Re: Generating an Formula using concatenation in excel

While I was using the Defined Names in my computer it was working fine but now i am trying to share the same to my team members by posting the workbook to a teams channel it is not working for others. Is there something I need to do to make it work on other Computers As well.?

# Re: Generating an Formula using concatenation in excel

If they open the file in Teams, which is actually Excel for web, EVALUATE, since VBA, doesn't work. That's only to open in Desktop app within Teams, or use Office Script. Assuming everyone is on corporate subscription, Office Script is not available for consumers.

1 best response

Accepted Solutions
best response confirmed by AjayGujay (Copper Contributor)
Solution

# Re: 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.