Validate column with Excel formula increments the formula is expected behavior or bug?

Question

I'm trying to create a spreadsheet where the first sheet ("Catalog") contains some pre-filled and some empty values in a column. I want the values to be in a drop down list that are restricted to values found in the second sheet ("Products").

I would expect that if I set the the Excel validation formula for cells "A1:A1048576" in the "Catalog" sheet to be a list validation of "Products!A1:A100" that every cell would only allow values from "Products!A1:A100". However, I'm finding that my formula gets incremented for every row in the "Catalog" sheet (i.e. In row 2 the formula becomes "Products!A2:A101", in row 3 the formula becomes "Products!A3:A102").

If version matters I'm using EPPlus.Core v1.5.4 from NuGet.

I'm not sure if this is a bug or if I'm going about applying my formula wrong?

I've already tried directly applying the validation to every cell in the column one cell at a time. I found that not only does it moderately increase the size of the resulting Excel file but more importantly it also exponentially increases the time taken to generate the Excel file. Even applying the validation one cell at a time on the first 2000 rows more than doubles the generation time.

ExcelPackage package = new ExcelPackage();
int catalogProductCount = 10;
int productCount = 100;

var catalogWorksheet = package.Workbook.Worksheets.Add($"Catalog");

for (int i = 1; i <= catalogProductCount; i++)
{
     catalogWorksheet.Cells[i, 1].Value = $"Product {i}";
}

var productsWorksheet = package.Workbook.Worksheets.Add($"Products");

for (int i = 1; i <= productCount; i++)
{
     productsWorksheet.Cells[i, 1].Value = $"Product {i}";
}

var productValidation = catalogWorksheet.DataValidations.AddListValidation($"A1:A1048576");
productValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
productValidation.ErrorTitle = "An invalid product was entered";
productValidation.Error = "Select a product from the list";
productValidation.ShowErrorMessage = true;
productValidation.Formula.ExcelFormula = $"Products!A1:A{productCount}";

Jason Jason · Accepted Answer · 2019-05-01T11:56:07

I guess I'm not that adept at Excel formulas.

Changing this line:

productValidation.Formula.ExcelFormula = $"Products!A1:A{productCount}";

to this:

productValidation.Formula.ExcelFormula = $"Products!$A$1:$A${productCount}";

stopped the auto increment issue. Hopefully this answer will save someone else some sanity as I wasted half a day on this issue myself.

Validate column with Excel formula increments the formula is expected behavior or bug?

1 Answers