Named range handling

Aspose.Cells Product Family
1

if I use Named Ranges in my formulas it does not work. if i replace range name by pecific cell it works .

commented lines uses cell id instead of named range:

License license = new License();

license.SetLicense("Aspose.Cells.lic");

Workbook workbook = new Workbook();

Worksheet orders = workbook.Worksheets[0];

Worksheet hidden = workbook.Worksheets.Add("hidden");

orders.Name = "orders";orders.Cells.CreateRange(

"A1", "A1").Name = "OPEN";orders.Cells["A1"].PutValue("enter 44 here");

//orders.Cells["B15"].Formula = "=IF(OPEN >= hidden!$B$14,\"MOO\", hidden!$B$14)";

//hidden.Cells["B14"].Formula = "=OPEN*1.2345";

orders.Cells["B15"].Formula = "=IF(orders!$A$1 >= hidden!$B$14,\"MOO\", hidden!$B$14)";

hidden.Cells["B14"].Formula = "=orders!$A$1*1.2345";workbook.Save(

"test8.xls");

return;

This message was posted using Aspose.Live 2 Forum
2

Hi,

Thanks for considering Aspose.

Yes I find an issue with calculating formulas involving named ranges.

We will figure out the issue soon.

Thank you.

3

Please try this version.

4

It works, thank you very much indeed. Would it be possible that you add this tiny enhancement where during save you will be allowed to specify for excel not to recalculate formulas?

Aistis

5

It does not work, check cell B16 after entering 44:

License license = new License();

license.SetLicense("Aspose.Cells.lic");

Workbook workbook = new Workbook();

Worksheet orders = workbook.Worksheets[0];

Worksheet hidden = workbook.Worksheets.Add("hidden");

orders.Name = "orders";

orders.Cells.CreateRange("A1", "A1").Name = "OPEN";

orders.Cells["A1"].PutValue("enter 44 here");

orders.Cells["B15"].Formula = "=IF(OPEN >= hidden!$B$14,\"MOO\", hidden!$B$14)";

hidden.Cells["B14"].Formula = "=OPEN*1.2345";

orders.Cells["B16"].Formula = "=hidden!$B$14";

//orders.Cells["B15"].Formula = "=IF(orders!$A$1 >= hidden!$B$14,\"MOO\", hidden!$B$14)";

//hidden.Cells["B14"].Formula = "=orders!$A$1*1.2345";

workbook.Save("test8.xls");

6

Hi,

Thanks for feedback,

We will figure it out soon.

Thank you.

7

Please try this attached fix. Thank you.

8

It seems to fix the problem, but I found another one, tell me why double brackets are invalid:

License license = new License();

license.SetLicense("Aspose.Cells.lic");

Workbook workbook = new Workbook();

Worksheet orders = workbook.Worksheets[0];

Worksheet hidden = workbook.Worksheets.Add("hidden");

orders.Name = "orders";

orders.Cells.CreateRange("A1", "A1").Name = "OPEN";

orders.Cells["A1"].PutValue("enter 44 here");

orders.Cells["B15"].Formula = "=IF(OPEN >= hidden!$B$14,\"MOO\", hidden!$B$14)";

hidden.Cells["B14"].Formula = "=OPEN*1.2345";

orders.Cells["B16"].Formula = "=hidden!$B$14";

hidden.Cells["M6"].Formula = "=OPEN*0.987654";

orders.Cells.CreateRange("A2", "A2").Name = "SWAPPOINTS";

orders.Cells["B17"].Formula = "=IF((IF(OPENLEVEL <= hidden!$M$6,\"MOO\",hidden!$M$6))=\"MOO\",\"MOO\",IF(OPENLEVEL <= hidden!$M$6,\"MOO\",hidden!$M$6) - SWAPPOINTS)";

//orders.Cells["B17"].Formula = "=IF(IF(OPENLEVEL <= hidden!$M$6,\"MOO\",hidden!$M$6)=\"MOO\",\"MOO\",IF(OPENLEVEL <= hidden!$M$6,\"MOO\",hidden!$M$6) - SWAPPOINTS)";

//orders.Cells["B15"].Formula = "=IF(orders!$A$1 >= hidden!$B$14,\"MOO\", hidden!$B$14)";

//hidden.Cells["B14"].Formula = "=orders!$A$1*1.2345";

workbook.Save("test8.xls");

return;

9

Hi,

I try to implement your scenario in MS Excel manually and then apply the formula i.e., :

"=IF((IF(OPENLEVEL <= hidden!$M$6,\"MOO\",hidden!$M$6))=\"MOO\",\"MOO\",IF(OPENLEVEL <= hidden!$M$6,\"MOO\",hidden!$M$6) - SWAPPOINTS)"

MS Excel also produces the formula error.

Could you check it in MS Excel.

Thank you.

10

try again (enter 44 in 2 cells)

License license = new License();

license.SetLicense("Aspose.Cells.lic");

Workbook workbook = new Workbook();

Worksheet orders = workbook.Worksheets[0];

Worksheet hidden = workbook.Worksheets.Add("hidden");

orders.Name = "orders";

orders.Cells.CreateRange("A1", "A1").Name = "OPENLEVEL";

orders.Cells["A1"].PutValue("enter 44 here");

orders.Cells["B15"].Formula = "=IF(OPENLEVEL >= hidden!$B$14,\"MOO\", hidden!$B$14)";

hidden.Cells["B14"].Formula = "=OPENLEVEL*1.2345";

orders.Cells["B16"].Formula = "=hidden!$B$14";

hidden.Cells["M6"].Formula = "=OPENLEVEL*0.987654";

orders.Cells.CreateRange("A2", "A2").Name = "SWAPPOINTS";

orders.Cells["A2"].PutValue("enter 44 here");

orders.Cells["B17"].Formula = "=IF((IF(OPENLEVEL <= hidden!$M$6,\"MOO\",hidden!$M$6))=\"MOO\",\"MOO\",IF(OPENLEVEL <= hidden!$M$6,\"MOO\",hidden!$M$6) - SWAPPOINTS)";

//orders.Cells["B17"].Formula = "=IF(IF(OPENLEVEL <= hidden!$M$6,\"MOO\",hidden!$M$6)=\"MOO\",\"MOO\",IF(OPENLEVEL <= hidden!$M$6,\"MOO\",hidden!$M$6) - SWAPPOINTS)";

//orders.Cells["B15"].Formula = "=IF(orders!$A$1 >= hidden!$B$14,\"MOO\", hidden!$B$14)";

//hidden.Cells["B14"].Formula = "=orders!$A$1*1.2345";

workbook.Save("test8.xls");

11

one more problem, enter 44 in 2 cells and check B18 cell

looks like you have big problem with unnecesarry brackets:

License license = new License();

license.SetLicense("Aspose.Cells.lic");

Workbook workbook = new Workbook();

Worksheet orders = workbook.Worksheets[0];

Worksheet hidden = workbook.Worksheets.Add("hidden");

orders.Name = "orders";

orders.Cells.CreateRange("A1", "A1").Name = "OPENLEVEL";

orders.Cells["A1"].PutValue("enter 44 here");

orders.Cells["B15"].Formula = "=IF(OPENLEVEL >= hidden!$B$14,\"MOO\", hidden!$B$14)";

hidden.Cells["B14"].Formula = "=OPENLEVEL*1.2345";

orders.Cells["B16"].Formula = "=hidden!$B$14";

hidden.Cells["M6"].Formula = "=OPENLEVEL*0.987654";

orders.Cells.CreateRange("A2", "A2").Name = "SWAPPOINTS";

orders.Cells["A2"].PutValue("enter 44 here");

orders.Cells["B17"].Formula = "=IF((IF(OPENLEVEL <= hidden!$M$6,\"MOO\",hidden!$M$6))=\"MOO\",\"MOO\",IF(OPENLEVEL <= hidden!$M$6,\"MOO\",hidden!$M$6) - SWAPPOINTS)";

hidden.Cells["K21"].Formula = "=OPENLEVEL*0.987654";

orders.Cells["B18"].Formula = "=(hidden!$K$21)*200000";

//orders.Cells["B17"].Formula = "=IF(IF(OPENLEVEL <= hidden!$M$6,\"MOO\",hidden!$M$6)=\"MOO\",\"MOO\",IF(OPENLEVEL <= hidden!$M$6,\"MOO\",hidden!$M$6) - SWAPPOINTS)";

//orders.Cells["B15"].Formula = "=IF(orders!$A$1 >= hidden!$B$14,\"MOO\", hidden!$B$14)";

//hidden.Cells["B14"].Formula = "=orders!$A$1*1.2345";

workbook.Save("test8.xls");

12

Thank you for the sample code.

In these days, we are working to enhance the formula calculation engine and we changed some formula parsing code which caused these problems. Please try this attached version.

13

B18 still does not work

14

Yes, we do have problems on processing unnecessary brackets. I will make a fix and more test on this issue. Hopefully I will provide you a new fix on next Monday. Thanks for your help.

15

Please try this attached version.

16

It seems to work, thank you.

A.

17

It works thank you!

18

One more problem, after entering 120 c3 should show #N/A

License license = new License();

license.SetLicense("Aspose.Cells.lic");

Workbook workbook = new Workbook();

Worksheet orders = workbook.Worksheets[0];

Worksheet hidden = workbook.Worksheets.Add("hidden");

orders.Name = "orders";

orders.Cells.CreateRange("A1", "A1").Name = "OPENLEVEL";

orders.Cells["A1"].PutValue("enter 120 here");

orders.Cells["C3"].Formula = "=ROUND(0.005*ROUNDDOWN(ROUND(MIN(OPENLEVEL,MIN(IF(111.153687602964>OPENLEVEL,OPENLEVEL,NA()),IF(110.958687602964>OPENLEVEL,OPENLEVEL,NA())))/0.005,9),0),9)";

workbook.Save("test8.xls");

return;

19

Hi,

We will figure it out soon.

Thanks for being patient!

20

We don't support NA function in previous versions. Now we make it. Please try this attached version.