We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Invalid Name error when using a SCAN and Lambda function

I’m generating Excel Workbooks that uses a formula that utilizes the SCAN() function. The SCAN funtion needs a LAMBDA function as input. For example:
=SCAN(0, columname, LAMBDA(acc,n,acc+n))
The Workbook is created without any errors, but upon opening the workbook in Excel the cells show “#NAME?”. When I click the cell I get a exclamation mark telling me there is a Invalid Name Error.
But the strange thing is that when I click the formulabar and hit enter, the cells show the correct value! The formula was not changed in any way (I’ve checked that multiple times). It just doesn’t seem to be evaluated.
I use the following code:
sheet.Cells[2, 2].SetDynamicArrayFormula("SCAN(0,column1,LAMBDA(acc,n,acc+n))", new FormulaParseOptions() { }, true);
Am I doing something wrong, or is the Scan or Lambda function not supported?
Thank you very much for any help on this.

@Verycrispy,

Thanks for the details.

As per the supported Excel formulas list, both SCAN and Lambda Excel 365 functions may not be supported by Aspose.Cells formula calculation engine. Do you need to only set the formulas (in Excel workbook to be calculated by MS Excel itself) or you want to calculate the formulas dynamically via Aspose.Cells formula calculation engine, please specify? Anyways, we are going to log a new ticket for your issue/requirements, so we could evaluate it in details.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

  Issue ID(s): CELLSNET-52620

You can obtain Paid Support services if you need support on a priority basis, along with the direct access to our Paid Support management team.

Hello @Amjad_Sahi,
Thank you for your quick reply.
For now I only need to set the formulas (in Excel workbook to be calculated by MS Excel itself). So if the user opens the workbook, they don’t see a ugly error and don’t need to press [Enter] behind the function in the formula bar.
If you want, I’m happy to share more details or the complete code I’m using.
Thank you.

@Verycrispy,

Thanks for your feedback.

We have noted it down and we will try to support setting your desired formulas to the cells in the first phase. Once we have an update on it, we will let you know.