Formula cell range referencing different worksheet

Aspose.Cells Product Family
1

Hello,

I have two worksheet, the first one with smartmarker that filled up a sheet with list of persons name per continent.

And in the second one I have summary page where I need to count the number of person per continent. The problem is that when I put the formula =COUNT(sheet1!A2:A2), the result after I do the export shows the same formula and not =COUNT(sheet1!A2:A30) , assuming that there is 30 row in the Sheet1.

The number 30 is just an example as this is a dynamic result.

Thanks for your help

2

Hi,


This may be due to the fact that by default, when the markers in the template file are processed, Aspose.Cells will first insert blank rows based on the number of records in the dataset/datatable lists or objects in the source, then data is inserted into those rows, so perhaps your formula becomes “=COUNT(sheet1!A2:A30)”. If you do not want Aspose.Cells to insert blank rows first and want to use the existing rows (in the template file sheet) as they are, you may use “noadd” parameter while defining smart markers in the template file sheet’s cells, e.g &=Person.Name(noadd). Now Aspose.Cells won’t insert blank rows anymore, so your formula won’t be expanded accordingly.

Hope, this helps.

Thank you.
3

Hello

I do want the formula to be expanded. The problem right now is that the formula is not expanded, it only counts one row instead of all the row that have been insert with the smartmarker.

I have attached a simplified template version.

Regards

4

Hi,


Sorry for the confusion.

Well, if you need the formulas should be expanded accordingly, please do not use “noadd” parameter.

I have checked your template file. I think for your needs, you should use the formula in the first worksheet i.e. “=COUNT(Sheet2!A2:A3)” instead of “=COUNT(Sheet2!A2:A2)”. It should work fine for your needs.

Let us know if you still have any problem or confusion.

Thank you.


5

Hi,

Unfortunately using =COUNT(Sheet2!A2:A3) does not fix the problem. After the excel file is generated the formula still shows "=COUNT(Sheet2!A2:A3) without any expansion.

Regards

6

Hi,


Thanks for your feedback and details.

I can notice the issue as you have mentioned. The formulas are not updated accordingly. Even I tried “noadd” parameter in the markers and inserted rows manually by using Cells.InsertRows(,true -->updateReference) based on the number of records in the DataTable.

Here is my simplest code.

Sample code:

//Create Students DataTable
DataTable dtEurope = new DataTable(“Europe”);

//Define a field in it
DataColumn dcName = new DataColumn(“employee”, typeof(string));
dtEurope.Columns.Add(dcName);

//Add three rows to it
DataRow drName1 = dtEurope.NewRow();
DataRow drName2 = dtEurope.NewRow();
DataRow drName3 = dtEurope.NewRow();

drName1[“employee”] = “John”;
drName2[“employee”] = “Jack”;
drName3[“employee”] = “James”;

dtEurope.Rows.Add(drName1);
dtEurope.Rows.Add(drName2);
dtEurope.Rows.Add(drName3);


string filePath = “e:\test2\AsposeSample2.xls”;

//Create a workbook from Smart Markers template file
Workbook workbook = new Workbook(filePath);

//Instantiate a new WorkbookDesigner
WorkbookDesigner designer = new WorkbookDesigner();

//Specify the Workbook
designer.Workbook = workbook;

// int nRows = dtEurope.Rows.Count;
// Worksheet sheet2 = workbook.Worksheets[1];
// sheet2.Cells.InsertRows(3, nRows-1, true);


//Set the Data Source
designer.SetDataSource(dtEurope);

//Process the smart markers
designer.Process();

//Save the Excel file
workbook.Save(filePath + “_out.xlsx”);

I have logged a ticket with an id “CELLSNET-41495” for your issue. We will look into your issue soon.

Thank you.
7

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please set designer.UpdateReference = true. It should resolve your issue. If you still encounter the issue, please feel free to post, we will be happy to assist you further.