This project has moved. For the latest updates, please go here.

EPPlus Formula value resulting in #VALUE!

Jan 31 at 5:27 AM
Edited Jan 31 at 5:29 AM
I have an Excel file with a table tblPhoneCode and with two columns Country and Code, and I have a cell B1 with List Data Validation pointing to the Country column

Image

and a cell B2 basically it displays the Code for the selected Country.

Image

Cell B2 uses the following formula
OFFSET(INDIRECT("tblPhoneCode[#Headers]"),MATCH(B1,INDIRECT("tblPhoneCode[Country]"),0),1,1,1)

Everything in Excel works as it should, but the issue is when I am reading the value of B2 using EPPlus in C# I am getting #VALUE! instead of the actual Phone Code. I've tried .Calculate() from workbook, worksheet, to cell and tried to access the value is still the same. I've attached the logger and it turns up empty and there is no error logged in it.

C# Code
static void Main(string[] args)
{
    var excelFile = new FileInfo(@"C:\Users\Ash\Desktop\Epplus.xlsx");
    using (var package = new ExcelPackage(excelFile))
    {
        // Output from the logger will be written to the following file
        var logfile = new FileInfo(@"C:\Users\Ash\Desktop\EpplusLogFile.txt");
        // Attach the logger before the calculation is performed.
        package.Workbook.FormulaParserManager.AttachLogger(logfile);
        // Calculate - can also be executed on sheet- or range level.
        package.Workbook.Calculate();

        Debug.Print(String.Format("Country: \t{0}", package.Workbook.Worksheets[1].Cells["B1"].Value));
        Debug.Print(String.Format("Phone Code:\t{0}", package.Workbook.Worksheets[1].Cells["B2"].Value));

        // The following method removes any logger attached to the workbook.
        package.Workbook.FormulaParserManager.DetachLogger();
    }
}
Output:
Country:    US
Phone Code: #VALUE!

Any help or insight is much appreciated, I am using MS Excel 2010, .NET 4.0, EPPlus 4.1.0, and Windows 10 64bit
Developer
Jan 31 at 9:29 PM
Hi,

It could be that the Indirect function in EPPlus cannot handle tablereferences. Have you tried without it?

If possible, create an issue and attach your workbook so we can reproduce the error.

/Mats
Feb 1 at 4:37 AM
Edited Feb 1 at 4:47 AM
I tried your suggestion and removed the table reference and used direct reference =OFFSET(G2:G5,MATCH(B1,F2:F5,0)-1,0,1,1) and it still returns #VALUE!, next I tried using named ranges =OFFSET(PhoneCodes,MATCH(B1,Countries,0)-1,0,1,1) and it also returns #VALUE! instead of the actual content
Feb 1 at 5:10 AM
Edited Feb 1 at 10:07 PM
Opened a new issue work item

Weirdly, it works when you don't have the package.Workbook.Calculate();