EPPLus Cell Formula #REF

Question

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!)

Are you sure the data you're referencing actually contains data that can be summed? Seems like this an issue with the existing spreadsheet and giving it an invalid lookup, rather than the code. — Rob, Dec 17 '15 at 03:55
Yes, there is data and that's why you see the last row in the excel file actually produces something. If I copy the formula from that last row upwards, it calculates properly. — Antonio Z, Dec 17 '15 at 04:07
Then can you show us the difference in the two formulas (the first one your code generates) and the one that works (when you copy it up)? And are you sure when copying you're not using the *exact* same formula including the rows? — Rob, Dec 17 '15 at 04:08
The only difference is the `$B(num)` - which suggests to me that the last row (let's say `$B16`) is valid but `$B14` is not - in any case we can't really help without seeing the excel file, as this question is entirely about excel and not related to `EPPlus` — Rob, Dec 17 '15 at 04:09
Hard to diagnose with without the exact same dataset/worksheet. Can you post the file somewhere? — Ernie S, Dec 17 '15 at 13:05

score 0 · Answer 1 · answered Mar 22 '18 at 22:28

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...

EPPLus Cell Formula #REF

1 Answers1