Formula using cell values between worksheets - problem

Sep 30, 2014 at 10:23 AM

I would like to count the data of cell values from another worksheet using a formula from my current worksheet.
  • another worksheets name is "Details - Calculation"
  • current worksheet name is "Statistics"
  • cells D7:D17 are in the worksheet "Details - Calculation"
  • cell D3 is on the current worksheet ("Statistics") and contains the string value I would like to count (ex. "apple")
I'm using the following formula to do this:
string formula = "COUNTIF($'Details - Calculation'.$D$7:$D$17;D3)";
worksheet.Cells[LastRow, FirstColumn].Formula = formula;
The problem is that when I open the file in OpenOffice, an extra parentheses is added to the end of the formula and the cell will contain the following error: #NAME?
When I delete this extra parentheses and I press enter, the calculation is correct.

I tried the following formula with Excel as well, but I got the message ""Excel found unreadable content in test.xlsx. Do you want to recocer the contents of this workbook? If you trust the source of this workbook, click Yes."" After I click yes, the cell is empty and doesn't contain the formula.

I tried the following formulas as well (cell A2 contains the text "Details - Calculation" and is in the current worksheet -> "Statistics"):
=COUNTIF($'Details - Calculation'.$D$7:$D$17;D3)  // OpenOffice
=COUNTIF(INDIRECT("'" & A2 & "'.$D$7:$D$17");D3) // OpenOffice

=COUNTIF(INDIRECT("'" & A2 & "'!$D$7:$D$17");D3) // Excel
=COUNTIF('Details - Visible Engine'!$D$8:$D$17;D3) // Excel
When I enter them manually, they work. When I do it with EPPlus (in this case I don't include the = sign) and I open the file I got the error message written above from Excel and Err:508 in the case of OpenOffice. In the case of OpenOffice when I go to the formula, I delete the dot, I write it again and I press enter, the error disappears and the calculation is correct.

Can anybody help me with this? Thank you in advance.
Oct 1, 2014 at 9:27 AM
I figured it out. If somebody has the same problem here is the solution:

Correct Excel formula:
=COUNTIF(INDIRECT("'" & A2 & "'!$D$7:$D$17");D3)
C# code wrong formula:
string formula = "COUNTIF(INDIRECT(\"'\" & A2 & \"'!$D$7:$D$17\");D3)";
C# code correct formula:
string formula = "COUNTIF(INDIRECT(\"'\" & A2 & \"'!$D$7:$D$17\"),D3)";
Conclusion: in the code we should use the comma instead of the semicolon. After opening the generated excel file the comma will be replaced by the semicolon, so the formula will be correct.

Note: in OpenOffice Calc instead of the exclamation mark (!) we should use the dot (.)
Marked as answer by csienke on 10/1/2014 at 2:27 AM
Oct 1, 2014 at 6:42 PM
It´s great to know that you solved your problem. This is a pretty common error among EPPlus users from non en-US culture.

If anybody find this thread with same issues, this tip, and others too, are in our EPPlus FAQ.

Take a look when you have a time =)