Adding Listvalidation to Excel

Dec 9, 2014 at 6:16 PM
Hi all ,

I was trying to do list validation using EPP plus. I tried the way be specifying formula as cell address.What happens is for each cell the list value decreases by a count.

EX the list has 1,2,3

First cell drop down 3 values ie . 1,2,3 .Second cell has only 2,3. and third cell only 3. I was able to replicate the same using the sample code by changing the "AddListValidation" to range ("A1:A4") . Thanks in advance.

private static void AddListValidationFormula(ExcelPackage package)
        var sheet = package.Workbook.Worksheets.Add("list formula");
        sheet.Cells["B1"].Style.Font.Bold = true;
        sheet.Cells["B1"].Value = "Source values";
        sheet.Cells["B2"].Value = 1;
        sheet.Cells["B3"].Value = 2;
        sheet.Cells["B4"].Value = 3;

        // add a validation and set values
        var validation = sheet.DataValidations.AddListValidation("A1:A5");
        // Alternatively:
        // var validation = sheet.Cells["A1"].DataValidation.AddListDataValidation();
        validation.ShowErrorMessage = true;
        validation.ErrorStyle = ExcelDataValidationWarningStyle.warning;
        validation.ErrorTitle = "An invalid value was entered";
        validation.Error = "Select a value from the list";
        validation.Formula.ExcelFormula = "B2:B4";

        Console.WriteLine("Added sheet for list validation with formula");

Jun 8, 2015 at 12:16 PM
Months later, no solution :((
ended up doing this:
i used a loop obviously in case i want to add a lot of items into the list
        var range = sheet.Cells["B2:B4"];

        for (int i = 0; i < range.Count(); i++)
Jun 10, 2015 at 9:56 AM
I can see two possible solutions:

A) use absolute cell addresses:

validation.Formula.ExcelFormula = "$B$2:$B$4";

B) use a named range:

sheet.Names.Add("SourceValues", ws.Cells["B2:B4"]);
validation.Formula.ExcelFormula = "=SourceValues";