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

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:
        validation.Formula.Values.Add(sheet.Cells["B2"].Value.ToString());
        validation.Formula.Values.Add(sheet.Cells["B3"].Value.ToString());
        validation.Formula.Values.Add(sheet.Cells["B4"].Value.ToString());
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++)
        {
            validation.Formula.Values.Add(sheet.Cells["B"+(i+2).ToString()].Value.ToString());
        }
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";