Hello,
I would like to count the data of cell values from another worksheet using a formula from my current worksheet.
Example:
 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.
