CODEWITHSUNDEEP
c#excelepplusepplus-4
Antonio Z
Antonio Z

Reputation: 11

EPPLus Cell Formula #REF

Hope someone can crack this issues with EPPLUS and Formulas. I'm getting an invalid #REF! when I try to assign a formula to a cell, yet the last row seems to accept the formula without a problem and does the calculations right.

Here is what the logic looks like at the time of assignment of the Formula. I am referencing data from another sheet.

        string formula1 = "";
        string formula2 = "";

        int uniqueTimeRow = 14;
        if (uniqueTimes.Rows.Count != 0)
        {
            foreach (DataRow row in uniqueTimes.Rows)
            {
                if (row["ExecutionTime"].ToString() != "")
                {
                    wsSummary.InsertRow(uniqueTimeRow, 1, uniqueTimeRow);
                    wsSummary.SetValue(uniqueTimeRow, 2, row["ExecutionTime"].ToString());

                    formula1 = "SUMIF(DataSummary[Strategy],$B" + uniqueTimeRow.ToString() + ",DataSummary[ExecQty])";
                    formula2 = "SUMIF(DataSummary[Strategy],$B" + uniqueTimeRow.ToString() + ",DataSummary[PrincipalAmount])";

                    wsSummary.Cells[uniqueTimeRow, 3].Formula = formula1;
                    wsSummary.Cells[uniqueTimeRow, 4].Formula = formula2;

                    uniqueTimeRow++;
                }
            }
        }

This is what the result excel file looks like.

Table Produced in Excel with the invalid #REF!

This is the Formula Produced on the Last Cell:

=SUMIF(DataSummary[Strategy],$B28,DataSummary[ExecQty])
=SUMIF(DataSummary[Strategy],$B28,DataSummary[PrincipalAmount])

If I copy these two formulas upwards this is what is produced as expected:

=SUMIF(DataSummary[Strategy],$B27,DataSummary[ExecQty])
=SUMIF(DataSummary[Strategy],$B27,DataSummary[PrincipalAmount])

When it has the invalid #REF! this is what shows up in the formula:

=SUMIF(#REF!,$B27,#REF!)

Upvotes: 1

Views: 1672

Answers (1)

Adam Willden
Adam Willden

Reputation: 87

Just hit the very same problem. I had it working and then it broke after refactoring. Before refactoring (by pure chance) I was copying rows upwards one at a time and this appears to be the only way it works properly.

To clarify you have to copy a row then paste it onto the row above. Now copy that row, and paste that onto the row above:

copy row 10, paste row 9, copy row 9, paste row 8... and so on...

Upvotes: 0

Related Questions