COUNTA cannot use references to named ranges

Aspose.Cells Product Family
1

Hi,

I’m attempting to create a dynamic named range by defining single cell named ranges (“start” at A1 and “end” at A7) and selecting all of the cells in between. The formula I’m using to define the dynamic range is =OFFSET(start,1,0,COUNTA(start:(end))-2,1) . This works correctly in Excel, but using Cells it returns a Range of $A$1:$A$2.

Through investigation I have determined that this is due to COUNTA(start:(end)) evaluating incorrectly. I set up a separate cell with a formula of =COUNTA(start:(end)) and this is still evaluating incorrectly using Aspose. If I modify these formulas to use COUNTA(A1:A7) instead of the references to start and end, it evaluates correctly in Aspose.

I will include the code snippet I was using to test below and will attach the spreadsheet I was using to test as well. This was testing using aspose-cells-8.0.0.jar. Any help would be appreciated.

Thanks,
Tim

Workbook work = new Workbook("/home/tcameron/Downloads/xlsOrderRangeTest.xlsx");
work.calculateFormula();
Range range = work.getWorksheets().getRangeByName(“testCountaRange”);
String startRef = work.getWorksheets().getRangeByName(“start”).getRefersTo();
String endRef = work.getWorksheets().getRangeByName(“end”).getRefersTo();

System.out.println("Starts at " + startRef + " and ends at " + endRef + " range size is " + range.getRowCount() + " range is referring to " + range.getRefersTo());
System.out.println("testCounta= " + work.getWorksheets().getRangeByName(“testCounta”).get(0, 0).getDisplayStringValue());

Output with reference to named ranges:

Starts at =Sheet1!$A$1 and ends at =Sheet1!$A$7 range size is 2 range is referring to =Sheet1!$A$1:$A$2
testCounta= 0

Output with reference to cells:

Starts at =Sheet1!$A$1 and ends at =Sheet1!$A$7 range size is 5 range is referring to =Sheet1!$A$2:$A$6
testCounta= 7



2

Hi,


Thanks for providing us template file and sample code.

After an initial test, I observed the issue you mentioned by using your sample code with your template file. The COUNTA(start:(end)) formula is evaluated wrong. I got the output as following which seems incorrect:
Starts at =Sheet1!$A$1 and ends at =Sheet1!$A$7 range size is 2 range is referring to =Sheet1!$A$1:$A$2
testCounta= 0

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

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

Thank you.
3

Hi,

Thanks for using Aspose.Cells.

Please download and use the latest version: Aspose.Cells for Java (Latest Version) it should fix your issue.

4

Hi,

Thanks for the quick response. I have tested this and the cell formula COUNTA calculation has been fixed. Unfortunately it looks like this is still calculating incorrectly when it is inside an OFFSET() (i.e. the COUNTA in “OFFSET(start,1,0,COUNTA(start:(end))-2,1)” is still failing to return 5). The same test from my first comment still applies, the output is now:

Starts at =Sheet1!$A$1 and ends at =Sheet1!$A$7 range size is 2 range is referring to =Sheet1!$A$1:$A$2
testCounta= 7

When it should be (is in Excel):

Starts at =Sheet1!$A$1 and ends at =Sheet1!$A$7 range size is 5 range is referring to =Sheet1!$A$2:$A$6
testCounta= 7

Thanks!

5

Hi,


Thanks for your feedback.

I noticed the issue as you mentioned. The range (“testCountaRange”) is not referring to correct area, it is referring to:
=Sheet1!$A$1:$A$2 where it should refer to:
=Sheet1!$A$2:$A$6

I have reopened your issue “CELLSJAVA-40793” again, we will look into it to figure it out soon.

Thank you.


6

Hi,

Thanks for using Aspose.Cells.

Please download and try our latest version/fix:Aspose.Cells for Java (Latest Version)

We have fixed your issue now.

Let us know your feedback.

Thank you.

7

Hi Amjad,

v8.0.1.1 did indeed fix the issue and everything seems to be working correctly for me now. Thanks for the quick responses.

Tim

8

Hi,


Thanks for your feedback.

Good to know that your issue is resolved now by the new fix/version. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.
9

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

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