CODEWITHSUNDEEP
aspose-cells
Boris Callens
Boris Callens

Reputation: 93357

Cell.Formula gives #name error until user selects formula

In my program I create an excel file with a formula containing a reference to a table (listobject). When opening the resulting excel file the formula shows the #NAME? error until the user selects the formula and exits it again.

First I do:

worksheet.Cells["A2"].Formula = "=INDEX(personData, MATCH(B2, personData[displayName]), 0)";
workbook.CalculateFormula();

When I open the resulting workbook in excel the formula for A2 is slightly changed in capitalization: 

=INDEX(PERSONDATA,MATCH(B2,personData[displayName]),0)

When focusing the formula and exiting again the formula becomes

=INDEX(personData,MATCH(B2,personData[displayName]),0)

What's the source of the capitalization?
What am I missing?

UPDATE
I've been able to narrow the problem down to the fact that the formula breaks when the referenced table is on another tab than the formula. When I move it to the same tab everything works as expected.
Updating the aspose.cells library to the most current version of aspose.cells (8.6.0.0) didn't help. Adding a tab reference to the referenced (lookup!personData) name is invalid.

Posted short post and example code on Aspose forum as requested: http://www.aspose.com/community/forums/permalink/658097/658097/showthread.aspx#658097

Upvotes: 0

Views: 764

Answers (1)

shakeel
shakeel

Reputation: 1725

It seems to me a bug of Aspose.Cells. Please try the latest version: Aspose.Cells for .NET 8.5.2 (download from Aspose website) and it should fix this issue. Please also post this issue in the Aspose.Cells forum with your sample excel file so that we could run your code and find out the root cause of the problem.

Mostly, such an error means the Aspose.Cells is unable to calculate the formula. Could you please also save your workbook in PDF format and see does it show the correct value or error.

Note: I am working as Developer Evangelist at Aspose

Upvotes: 2

Related Questions