By Isaac Little


2017-11-06 20:11:04 8 Comments

I have been using EPPlus to export an excel worksheet that logs the results of various tests and then does a basic summary of them. I have these lines of code that work well

groupsheet.Cells[3, 5, 3, (25 + (tPts.Count() - 1) * 9) ].Formula = "AVERAGE(E6:E"+(5+ mCount).ToString()+")";
groupsheet.Cells[4, 5, 4, (25 + (tPts.Count() - 1) * 9) ].Formula = "STDEV(E6:E"+(5+ mCount).ToString()+")";
groupsheet.Cells[5, 5, 5, (25 + (tPts.Count() - 1) * 9) ].Formula = "MAX(E6:E"+(5+ mCount).ToString()+")";

As I have come to expect from EPPlus, that code ranges. So E3 gets "=Average(E6:E8)" (if mCount is 3), F3 gets "=Average(F6:F8)" etc. all the way to the end (as defined by the length of tPts). Similarly everything in row 4 gets the standard deviation of the desired range and everything in row 5 gets the max of the desired range.

I also have an overall summary sheet that records the data from all individual group sheets. I am currently doing this by copying over the exact values with the following code.

summarysheet.Cells[3 + groupCount * 4, 5, 5 + groupCount * 4, (25 + (tPts.Count() - 1) * 9)].Value = groupsheet.Cells[3 , 5, 5 , (25 + (tPts.Count() - 1) * 9)].Value;

This works as expected putting the value from the individual groupsheet into the right place on the summary sheet. But I would like it if each cell contained a reference to the appropriate cell on the individual group worksheet (titled Group1, Group2, Group3...) so that the summary sheet changed dynamically with changes made to the individual groupsheets.

My instincts say that the proper way to do this would be through the following code.

summarysheet.Cells[3 + groupCount * 4, 5, 5 + groupCount * 4, (25 + (tPts.Count() - 1) * 9)].Formula = "Group"+ groupNumKey.ToString() +"!E3";

However this just pastes the formula for cell E3 into every single cell. I can't figure out how to make the formula calls range similarly to how they do when it's all contained within one worksheet.

Thanks so much!

This is my attempt at a brute force method

// Iterate over each column to put the references in place
int currColNum = 5; //Start at 5 (E)
string currColName = "E";
int maxColNum = (25 + (tPts.Count() - 1) * 9); // End at the last column
while (currColNum <= maxColNum)
{
    currColName = Base26Encode(currColNum); // Finds the excel column name from the row #
    summarysheet.Cells[3 + groupCount * 4, currColNum].Formula = "Group" + groupNumKey.ToString() + "!" + currColName + "3"; // Grab Average
    summarysheet.Cells[4 + groupCount * 4, currColNum].Formula = "Group" + groupNumKey.ToString() + "!" + currColName + "4"; // Grab STD
    summarysheet.Cells[5 + groupCount * 4, currColNum].Formula = "Group" + groupNumKey.ToString() + "!" + currColName + "5"; // Grab Max

    currColNum++;
}

where "Base26Encode" is a piece of code that outputs A when given a 1, Z when given a 26, AA when given 27, and so on (converting column indexes into excel column names)

1 comments

@Ernie S 2017-11-06 22:27:55

If you want to ref cells in another sheet you seem to have the formula right but you have hard-coded !E3 into it so it make sense that the formula will equal `GroupX!E3". Seems like all you need to do is change this:

summarysheet.Cells[3 + groupCount * 4, 5, 5 + groupCount * 4, (25 + (tPts.Count() - 1) * 9)].Formula 
    = "Group" + groupNumKey.ToString() +"!E3";

to this:

//Use a variable to save space and avoid repeating the same calculation over and over
var row = (25 + (tPts.Count() - 1) * 9);
summarysheet.Cells[3 + groupCount * 4, 5, 5 + groupCount * 4, row].Formula 
    = "Group" + groupNumKey.ToString() +"!E" + row; 

Let me know if I am misunderstanding.


Response To Comments: @Iassac I am still having a little trouble understanding what you are trying to do. Maybe post more code as it is a little hard to grasp without more. If it helps, here is a unit test with what I came up with which does allow me to ref a cell in another sheet that has a formula:

[TestMethod]
public void SheetReferenceTest()
{
    //https://stackoverflow.com/questions/47144930
    var file = new FileInfo(@"c:\temp\SheetReferenceTest.xlsx");
    if (file.Exists)
        file.Delete();

    using (var pck = new ExcelPackage(file))
    {
        var group = 1;
        var workbook = pck.Workbook;    
        var group1 = workbook.Worksheets.Add($"group{group}");
        var summarysheet = workbook.Worksheets.Add("summarysheet");

        group1.Cells["A1"].Value = 2;
        group1.Cells["A2"].Value = 3;
        group1.Cells["A3"].Value = 6;
        group1.Cells["A4"].Value = 27;
        group1.Cells["A5"].Formula = "Average(A1:A4)";

        //This shows "=group1!A5" in Excel when the cell is selected
        summarysheet.Cells["A1"].Formula = $"group{group}!A5";

        pck.Save();
    }
}

RESPONSE TO EDIT

Ok, now I get what you are after. Seems you are correct, I cannot get excel to handle the shared string formula. I am not sure this is an Epplus problem as I do not even know if this is something that excel can do? Here is the unit test I came up with the show the problem:

[TestMethod]
public void SheetReferenceTest()
{
    //https://stackoverflow.com/questions/47144930
    var file = new FileInfo(@"c:\temp\SheetReferenceTest.xlsx");
    if (file.Exists)
        file.Delete();

    using (var pck = new ExcelPackage(file))
    {
        var group = 1;
        var workbook = pck.Workbook;    
        var group1 = workbook.Worksheets.Add($"group{group}");
        var summarysheet = workbook.Worksheets.Add("summarysheet");
        var random = new Random();

        const int rows = 10;
        const int cols = 15;
        for (var r = 0; r < rows; r++)
            for (var c = 0; c < cols; c++)
                group1.Cells[r + 1, c + 1].Value = random.Next(100);

        //This works fine and auto increments the formala to B1:B10, C1:C10, etc.
        group1.Cells[rows + 1, 1, rows + 1, cols].Formula = $"AVERAGE(A1:A{rows})";

        //This does not, it just does group1!A1:A10 for all cells.
        summarysheet.Cells[rows + 1, 1, rows + 1, cols].Formula = $"AVERAGE(group1!A1:A{rows})";

        pck.Save();
    }
}

Here is what the xml looks like for the WORKING sheet:

<row r="11">
    <c r="A11" s="0">
        <f ref="A11:O11" t="shared" si="1">AVERAGE(A1:A10)</f>
    </c><c r="B11" s="0">
        <f t="shared" si="1"/>
    </c><c r="C11" s="0">
        <f t="shared" si="1"/>
    </c><c r="D11" s="0">
        <f t="shared" si="1"/>
    </c><c r="E11" s="0">
        <f t="shared" si="1"/>
    </c><c r="F11" s="0">
        <f t="shared" si="1"/>
    </c><c r="G11" s="0">
        <f t="shared" si="1"/>
    </c><c r="H11" s="0">
        <f t="shared" si="1"/>
    </c><c r="I11" s="0">
        <f t="shared" si="1"/>
    </c><c r="J11" s="0">
        <f t="shared" si="1"/>
    </c><c r="K11" s="0">
        <f t="shared" si="1"/>
    </c><c r="L11" s="0">
        <f t="shared" si="1"/>
    </c><c r="M11" s="0">
        <f t="shared" si="1"/>
    </c><c r="N11" s="0">
        <f t="shared" si="1"/>
    </c><c r="O11" s="0">
        <f t="shared" si="1"/>
    </c>
</row>

But for the non-working summary sheet:

<row r="11">
    <c r="A11" s="0">
        <f ref="A11:O11" t="shared" si="1">AVERAGE(group1!A1:A10)</f>
    </c><c r="B11" s="0">
        <f t="shared" si="1"/>
    </c><c r="C11" s="0">
        <f t="shared" si="1"/>
    </c><c r="D11" s="0">
        <f t="shared" si="1"/>
    </c><c r="E11" s="0">
        <f t="shared" si="1"/>
    </c><c r="F11" s="0">
        <f t="shared" si="1"/>
    </c><c r="G11" s="0">
        <f t="shared" si="1"/>
    </c><c r="H11" s="0">
        <f t="shared" si="1"/>
    </c><c r="I11" s="0">
        <f t="shared" si="1"/>
    </c><c r="J11" s="0">
        <f t="shared" si="1"/>
    </c><c r="K11" s="0">
        <f t="shared" si="1"/>
    </c><c r="L11" s="0">
        <f t="shared" si="1"/>
    </c><c r="M11" s="0">
        <f t="shared" si="1"/>
    </c><c r="N11" s="0">
        <f t="shared" si="1"/>
    </c><c r="O11" s="0">
        <f t="shared" si="1"/>
    </c>
</row>

@Isaac Little 2017-11-07 19:06:33

Hi @Ernie , I'm not sure I explained my problem well. I would like to have summary sheet cell E3 contain a reference to E3 on the group sheet, have summary sheet cell F3 contain a reference to F3 on the group sheet, and so on up to whatever the last column in the data is. Despite looking hard coded when I used the first 3 lines the columns dynamically changed so E5 = Avg(E6:E8), F5 = Avg (F6:F8), excreta. However that doesn't work when I am trying to reference the different sheet and I don't know if that is something I can change or not

@Ernie S 2017-11-07 23:03:48

@IsaacLittle See my RTC above.

@Isaac Little 2017-11-07 23:16:55

Hi @Ernie, so I found a simply workaround by brute forcing it. Maybe If I show you that it will help clarify. I will add that to the main text now.

@Isaac Little 2017-11-10 20:58:09

@ Ernie, Thanks so much for all your help! I guess I'll just have to iterate through each manually.

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] Excel removes formula added with EPPlus

1 Answered Questions

[SOLVED] Excel table created by EPPlus doesn't propagate formula to new rows

  • 2019-05-01 02:45:43
  • sasfrog
  • 177 View
  • 1 Score
  • 1 Answer
  • Tags:   c# .net excel epplus

4 Answered Questions

[SOLVED] VBA copy range of values not destroying formulas

  • 2015-08-20 09:28:30
  • Przemyslaw Remin
  • 1961 View
  • 0 Score
  • 4 Answer
  • Tags:   excel vba

3 Answered Questions

[SOLVED] Get content of a cell given the row and column numbers

1 Answered Questions

[SOLVED] EPPlus clone worksheet

  • 2018-02-01 09:37:01
  • Bùi Đức Khánh
  • 6276 View
  • 5 Score
  • 1 Answer
  • Tags:   c# epplus npoi

1 Answered Questions

[SOLVED] Column doesn't execute formula Excel/C#/EPPlus

  • 2017-06-21 13:24:49
  • J. Lavoie
  • 1035 View
  • 0 Score
  • 1 Answer
  • Tags:   c# excel epplus

1 Answered Questions

[SOLVED] Adding a formula to Excel worksheet results in HRESULT: 0x800A03EC

1 Answered Questions

[SOLVED] EPPlus formatting value from formula

  • 2015-11-21 14:21:55
  • GreenGeckoZA
  • 798 View
  • 1 Score
  • 1 Answer
  • Tags:   c# epplus

1 Answered Questions

[SOLVED] Copying Dynamic Range Between Excel Worksheets

1 Answered Questions

Excel formula sumproduct consuming more time (EPPlus)

  • 2014-05-29 10:39:46
  • atulya
  • 242 View
  • 0 Score
  • 1 Answer
  • Tags:   excel epplus

Sponsored Content