Smart Markers - Array Formula

Aspose.Cells Product Family
1

Hi,
I am trying to use an array formula in a smartmarker. Is there anyway I would be able to do this?
Final formula should look something like the below:
=SUM((TimeCostData[Product UID]=D{r})(TimeCostData[Month]=MTH!C3)(TimeCostData[Total]))

2

Hi,


Thanks for your query.

In Smart Markers, there are two types of dynamic formulas that can be used:
1) A formula starts with &== is called DynamicFormula which will be processed only once for the cell where it is pasted.

2) A formula starts with &=&= is called RepeatDynamicFormula which will be processed repeatedly for each row in the data source.

Please see the document for your complete reference on Smart Markers and dynamic formulas:

Hope, this helps a bit.

Thank you.

3

Thank you for the reply though perhaps I have not been clear in my requirements. I am trying to use an Excel array formula where basically the formula is wrapped within curly braces. I would normally create it by pressing Ctrl Shift and Enter when I have finished entering the formula

4

Hi,


Could you provide us a template Excel file, output file (by Aspose.Cells APIs after markers are processed to fill the data into the cells) and create your expected file manually in MS Excel. Also you may paste a sample code (runnable), so we could check and evaluate your issue precisely to figure it out soon.

Thank you.
5

Hi,


Please find attached a sample of the output I am trying to produce. Due to the sensitivity of the information, I have had to attach a bare bones file but this still highlights the formulas I am trying to create as dynamic formula smartmarkers (See column C of the MTH sheet).

Notice the ‘{}’ symbols around the formula denoting it is an array formula :




{=SUM((TimeCostData[Product UID]=B11)(TimeCostData[Month]=$C$3)(TimeCostData[Total]))}




My attempt at a smart marker formula is:


&=&=SUM((TimeCostData[Product UID]=B{r})(TimeCostData[Month]=$C$3)(TimeCostData[Total]))


but this is not processed as an array formula (missing the curly braces)

6

Hi,


Thanks for providing us sample file.

We need to evaluate your requirements/issue thoroughly. I have logged a ticket with an id “CELLSNET-44523” for your issue/requirements. We will look into it soon. We might either provide you sample code (as a workaround) to perform your task with current APIs set or we will incorporate this enhancement to Aspose.Cells APIs and provide you a fixed/supported version soon.

Once we have an update on it, we will let you know here.

Thank you.
7

Hi,

Thanks for your using Aspose.Cells.

Please download and try the latest fix:
Aspose.Cells for .NET v8.8.2.7 and let us know your feedback.

Please try the following code with the attached file.

C#

var workbook = new Workbook(path + “SmartArrayFormula.xlsx”);
DataTable dt = workbook.Worksheets[0].Cells.ExportDataTable(10, 1, 30, 1);
dt.TableName = “table1”;
WorkbookDesigner d = new WorkbookDesigner(workbook);
d.SetDataSource(dt);
d.Process();

8

Thank you very much for sending this through.
Can I just check, is this fix a one-off to fit my purpose or will this become part of the next release of Aspose and versions going forwards?
I would just be a little reluctant to use this if it then ties me into this special version of Aspose

9

Hi Phil,


Thank you for your feedback.

Please note, the fix has been provided on the top of latest revision of Aspose.Cells for .NET and will be part of the next major release of the API. This will also propagate to the future public releases.

Hope this helps a bit.
10

The issues you have found earlier (filed as CELLSNET-44523) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.