Cells.NET- calculation problem

Aspose.Cells Product Family
1

Hi, I am currently trying to use Aspoce.Cells .NET for calculation purposes. What I do is to transfer data to a template workbook that I want to execute formulas on then return the result to my application. But I can’t get this working 100%.

What I want to do is to use a combination of match and index to find a value based on 2 keys.

Example:

sheet BigBags:

Type ; Weight ; ProductKey

A ; 100 ; 132425

A ; 120 ; 124666

B ; 100 ; 476128

sheet CalclationSheet:

A ; B ; C ; D ; ….

SomeLine ; A ; 120 ; [Formula] ; … Other values that use the result of formula…

This is the formula I use (tested ok on excel 2003)

=INDEX(BigBags!A2:C29; MATCH(B1&C1;BigBags!A2:A29&BigBags!B2:B29;0); 3)

I use Workbook.CalculateFormula() to execute the formulas, and it executes with no exception, but the result it returns holds the value: “#VALUE!”. I stored the workbook and opened it in excel where it shows value, if the formula don’t return a key in excel it shows “#N/A”.

Is there a fix or workaround to this problem?

2

I put your values into an Excel file and it does return "#VALUE" in MS Excel. Please check the attached file.

Could you please post your file here?

3

ty for the quick reply.

The original file is a huge and messy production sheet… I remade the one that you posted so it works; the formula is an Excel matrix formula so you have to press Shift+Ctrl+Enter when you have typed it.

The attached file should work.

4

Please try this attached fix.

5

The fix works fine. Thank you.