INDIRECT() function does not support named ranges

Aspose.Cells Product Family
1

I seems that the INDIRECT function does not work when the parameter refers to a named range.

Code snippet:

Workbook workbook = new Workbook();
Worksheet sheet1 = workbook.getWorksheets().getSheet(0);
sheet1.getCells().getCell(“A1”).setValue(123);
NamedRange namedRange = sheet1.getCells().createNamedRange(“TestRange”,“A1”, “A1”);
sheet1.getCells().getCell(“B1”).setFormula("=INDIRECT(“TestRange”)");
sheet1.getCells().getCell(“B2”).setFormula("=INDIRECT(“A1”)");
workbook.calculateFormula(false);
System.out.println("Value using range: "+sheet1.getCells().getCell(“B1”).getValue());
System.out.println("Value direct: "+sheet1.getCells().getCell(“B2”).getValue());

Result:
Value using range: #REF!
Value direct: 123

It looks like this bug (and the issue that SQRT does not support single-cell range) are the only two blocking issues left for us…

With kind regards,

Barry

2

Hi Barry,

We are able to reproduce the issue. The issue has been logged into our Issue Tracking System with ID CELLSJAVA-20158. We will update you as soon as possible.

Thanks,

3

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

This message was posted using Notification2Forum from Downloads module by aspose.notifier.
4

In release 2.4.1, it seems the ‘fix’ to the INDIRECT() function has broken other features:

When the parameter to INDIRECT() is a cell reference (instead of a string), then the result is a #REF error:

Workbook workbook = new Workbook();
workbook.setCalculationMode(CalculationMode.MANUAL);
Worksheet sheet1 = workbook.getWorksheets().getSheet(0);
sheet1.getCells().getCell(“A1”).setValue(123);
sheet1.getCells().getCell(“A2”).setFormula("=INDIRECT(A1)");
sheet1.getCells().getCell(“A3”).setFormula("=INDIRECT(“A1”)");
workbook.calculateFormula();
System.out.println("Value A2: "+sheet1.getCells().getCell(“A2”).getValue());
System.out.println("Value A3: "+sheet1.getCells().getCell(“A3”).getValue());

Results in:
Value A2: #REF!
Value A3: 123

(This was working fine in 2.4.0.7)

Barry

5

Hi Barry,

Well, I think it is not an issue. Aspose.Cells works in the same way as MS Excel does. You may manually try the formula in a sheet (in MS Excel) after putting 123 into A1 cell:
i.e. “=INDIRECT(A1)” —> it will give you #REF error too in MS Excel.
When you give A1 surrounded with double quotes, it works fine in MS Excel too. So, Aspose.Cells for Java does the same as MS Excel regarding INDIRECT formula.

Thank you.


6

Amjad,

You are correct, Aspose and Excel behave the same. I was seeing weird results, which put me on the wrong track.

It seems there is something weird going on with the use of INDIRECT():

Given this snippet:
Workbook workbook = new Workbook();
Worksheet sheet1 = workbook.getWorksheets().getSheet(0);
sheet1.getCells().getCell(“A1”).setValue(123);
NamedRange namedRange = sheet1.getCells().createNamedRange(“TestRange”,“A1”, “A1”);
sheet1.getCells().getCell(“B1”).setFormula("=INDIRECT(“TestRange”)");
sheet1.getCells().getCell(“A2”).setFormula("=INDIRECT(“TestRange”)");
workbook.calculateFormula(false);
System.out.println("Value: "+sheet1.getCells().getCell(“B1”).getValue());
System.out.println("Value: "+sheet1.getCells().getCell(“A2”).getValue());

The result is:
Value: 123
Value: #VALUE!

I’m not sure what is happening here, but for some reason INDIRECT() does not compute when it stored on a different row?!? Very strange behavior…

With kind regards,

Barry

7

Hi,


Yes, we confirmed, for the issue of formula:

=INDIRECT("TestRange"), it is an issue of calculating Range parameters that only contain one cell and this bug has been there for all versions. We will fix it soon in next fix.

Thank you.
8

Hi Barry,

Please use the updated version aspose-cells-2.4.1.1-java.zip attached. The issue has been fixed.

Thanks,

9

With the new build I’m getting a classcastexception:

Exception in thread “main” com.aspose.cells.FormulaCalcException: SF.SCR_G!H5[=SCR<>0]: SF.SCR_G!F60[=BSCR+IF(SUM(Adj),SUM(Adj),SUM(Adj.Modular))+SCRop]: SF.SCR_G!F82[=MIN(SUM(AdjTP.Modular,AdjDT.modular),0)]: SF.SCR_G!F252[=IF(ISBLANK(nBSCR),0,-MIN(BSCR-nBSCR,FDB))]: SF.SCR_G!F70[=F71+SCRint]: SF.SCR_G!F71[=SQRT(MMULT(TRANSPOSE(1F74:F78),J74:J78))]: SF.SCR_G!F74[=nSCRmkt]: SF.SCR_G!F281[=F293+F282]: SF.SCR_G!F293[=SQRT(MMULT(TRANSPOSE(1F296:F302),J296:J302))]: SF.SCR_G!F297[=nMKTeq]: SF.SCR_G!F322[=SUM(F323:F324)]: SF.SCR_G!F324[=F337]: SF.SCR_G!F337[=SQRT(MMULT(TRANSPOSE(1F340:F341),J340:J341))]: SF.SCR_G!J340[=MMULT(1$N$334:$O$335,1*F340:F341)]: com.aspose.cells.fU cannot be cast to [[Lcom.aspose.cells.ex;
at com.aspose.cells.Cell.a(Unknown Source)
at com.aspose.cells.Cell.h(Unknown Source)
at com.aspose.cells.Cell.g(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.ex.f(Unknown Source)
at com.aspose.cells.hH.c(Unknown Source)
at com.aspose.cells.hH.a(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.a(Unknown Source)
at com.aspose.cells.Cell.h(Unknown Source)
at com.aspose.cells.Cell.g(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.ex.h(Unknown Source)
at com.aspose.cells.Cell.a(Unknown Source)
at com.aspose.cells.Cell.g(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.ex.f(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.hH.i(Unknown Source)
at com.aspose.cells.hH.a(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.a(Unknown Source)
at com.aspose.cells.Cell.g(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.ex.h(Unknown Source)
at com.aspose.cells.Cell.a(Unknown Source)
at com.aspose.cells.Cell.g(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.ex.f(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.a(Unknown Source)
at com.aspose.cells.Cell.h(Unknown Source)
at com.aspose.cells.Cell.g(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.ex.h(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.ex.b(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.a(Unknown Source)
at com.aspose.cells.Cell.g(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.ex.h(Unknown Source)
at com.aspose.cells.Cell.a(Unknown Source)
at com.aspose.cells.Cell.g(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.ex.f(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.a(Unknown Source)
at com.aspose.cells.Cell.h(Unknown Source)
at com.aspose.cells.Cell.g(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.ex.h(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.ex.b(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.a(Unknown Source)
at com.aspose.cells.Cell.g(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.ex.h(Unknown Source)
at com.aspose.cells.hH.j(Unknown Source)
at com.aspose.cells.hH.a(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.a(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.a(Unknown Source)
at com.aspose.cells.Cell.g(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.ex.h(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.hH.i(Unknown Source)
at com.aspose.cells.hH.a(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.a(Unknown Source)
at com.aspose.cells.Cell.g(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.ex.h(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.hH.i(Unknown Source)
at com.aspose.cells.hH.a(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.a(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.a(Unknown Source)
at com.aspose.cells.Cell.g(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.ex.h(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.ex.b(Unknown Source)
at com.aspose.cells.ex.a(Unknown Source)
at com.aspose.cells.bN.b(Unknown Source)
at com.aspose.cells.bN.a(Unknown Source)
at com.aspose.cells.Cell.g(Unknown Source)
at com.aspose.cells.fO.a(Unknown Source)
at com.aspose.cells.fO.a(Unknown Source)
at com.aspose.cells.eG.a(Unknown Source)
at com.aspose.cells.Workbook.calculateFormula(Unknown Source)
at com.aspose.cells.Workbook.calculateFormula(Unknown Source)

With kind regards,

Barry

10

Hi,

Could you give us sample code and template file(if you have) to reproduce the issue, we will check your issue soon.

Thank you.

11

I’ve added the sample code and the Excel template file.

Barry

12

Hi Barry,

I have found the issue using your sample code and template file after an initial test. We will figure your issue out and get back to you soon.

Thank you.

13

Hi,

Please try the attached version, we have fixed your mentioned issue.

Thank you.

14

Hi,

We have also released our next official release of the product v2.4.2, so you may try it:
http://www.aspose.com/community/files/72/java-components/aspose.cells-for-java/entry265363.aspx

Thank you.