EPPlus Problem Creating List Validation from Table on another spreadsheet

[rachael-ross]

I've created a table on one worksheet (list of state names) and named the table "tblStates". Then, on a second worksheet, there is a column for State where I want list validation for those cells, in which each cell has a dropdown offering the state names from the "tblStates" table. So for the State column in the 2nd worksheet, I created a table named "tblGeoStates" and added list validation to the column that references "=tblStates". (I referenced this article/video, although I don't have a 2nd dependent list, but just wanted to do the first part of this: Contexture Video)

By following the steps from the link above, I've performed these steps manually in Excel and it works perfectly, however, when coding it via EPPlus, it doesn't work. The tables in each worksheet are present and named correctly. The list validation has been applied to the table in the 2nd worksheet and a dropdown appears on each cell in tblGeoStates. But there are no values being displayed.

Worksheet 1 with tblStates table

Worksheet 2 with tblGeoStates table with list validation that references the first table

When I try to click on the list validation dialog to close it, I receive this validation error from Excel - None of this happens in the manually created workbook and everything appears to be the same

Code using EPPlus that generates each table and then the list validation on the 2nd table.

        // Add Named Table for States column so we can reference it in another sheet
        using (ExcelRange r = ws.Cells[$"B6:B57"])
        {
            ExcelTableCollection tblcollection = ws.Tables;
            ExcelTable table = tblcollection.Add(r, "tblStates");                             
        }

        // Add Named Table for GeoStates column and validation that references tblStates
        using (ExcelRange r = ws.Cells["F:F"]) // also tried "F2:F1048576 so the header isn't included
        {
            ExcelTableCollection tblcollection = ws.Tables;
            ExcelTable table = tblcollection.Add(r, "tblGeoStates");
            table.ShowFilter = false;

            var v1 = r.DataValidation.AddListDataValidation();               
            v1.ShowErrorMessage = true;
            v1.ErrorStyle = ExcelDataValidationWarningStyle.warning;
            v1.ErrorTitle = "An invalid value was entered";
            v1.Error = "Select a value from the list";
            v1.Formula.ExcelFormula = "=tblStates"; // tried w & w/o = sign
        }

I've tried a few different things like including the header or not in the selected range for the tables; AllowedBlanks or not, showed filter or not, etc.

I've examined the underlying XML of both the manually created workbook as well as the one generated programmatically with EPPlus and they appear different.

This is the EPPlus auto-generated one (doesn't work):

<dataValidation sqref="F1:F1048576" errorStyle="warning" error="Select a value from the list" errorTitle="An invalid value was entered" showErrorMessage="1" type="list">
<formula1>tblStates</formula1>
</dataValidation>

And this is the one I did manually (and works):

<dataValidation xr:uid="{895EE9F3-E8D3-45B6-AD98-9A095C448064}" sqref="F1:F1048576" showErrorMessage="1" showInputMessage="1" type="list" allowBlank="1">
<formula1>tblStates</formula1>
</dataValidation>

[kimmania]

@rachael-ross I ran across this issue as well. I have a worksheet with my data for drop down lists. In my second worksheet, I want to use the values from the first sheet. I was able to work around the limitation of having the named range not being created at the "global" level by creating the named range on the second sheet instead of the first.

This worked for me:

        private static void CreateNamedRangeForSecondWorksheet(ExcelWorksheet sheet_configuration, ExcelWorksheet sheet_subjects)
        {
            // Add Named Range for States column so we can reference it in another sheet
            using (ExcelRange r = sheet_configuration.Cells[$"B6:B57"])
            {
                // Notice that I am putting the named range on a different sheet
                sheet_subjects.Names.Add("tblStates", r);
            }

            // Add Named Table for GeoStates column and validation that references tblStates
            using (ExcelRange r = sheet_subjects.Cells["F:F"]) // also tried "F2:F1048576 so the header isn't included
            {
                ExcelTableCollection tblcollection = sheet_subjects.Tables;
                ExcelTable table = tblcollection.Add(r, "tblGeoStates");
                table.ShowFilter = false;

                var v1 = r.DataValidation.AddListDataValidation();
                v1.ShowErrorMessage = true;
                v1.ErrorStyle = ExcelDataValidationWarningStyle.warning;
                v1.ErrorTitle = "An invalid value was entered";
                v1.Error = "Select a value from the list";
                v1.Formula.ExcelFormula = "tblStates"; // tried w & w/o = sign
            }
        }

This work around definitely does not work if you need the name range on multiple sheets; but in my case, I only needed it on the one.