Thanks for providing us template file.
Well, I checked your template file, you have actually specified List Data Validation to the B column in the first worksheet whose source is coming from Second worksheet’s range. I have implemented your scenario/ requirements using your template file and add data validation (List) to B2:B32 in the first worksheet. I re-inserted the source values in D column of the second worksheet and created the range based on the area (D1:D300), I added the Data Validation to the B column cells B2:B32 in the first worksheet based on the source range of the second worksheet, it works absolutely fine here. I have attached the output Excel file for your reference.
Workbook workbook = new Workbook(“e:\test2\sampleExcel.xls”);
//Get the second worksheet in the workbook
Worksheet worksheet2 = workbook.Worksheets;
//Populate the D column in the second sheet with data 300 Employees.
int i = 0;
for (i = 1; i <= 300; i++)
worksheet2.Cells[“D” + i].PutValue(“Emp00” + i);
Range range = worksheet2.Cells.CreateRange(“D1”, “D300”);
range.Name = “MyRange”;
//Get the first worksheet in the workbook
Worksheet worksheet1 = workbook.Worksheets;
ValidationCollection validations = worksheet1.Validations;
//Clear the existing validation if you want.
Validation validation = validations[validations.Add()];
validation.Type = Aspose.Cells.ValidationType.List;
validation.Operator = OperatorType.None;
validation.InCellDropDown = true;
validation.IgnoreBlank = true;
validation.Formula1 = “=MyRange”;
validation.ShowError = true;
validation.AlertStyle = ValidationAlertType.Stop;
validation.ErrorTitle = “Error”;
validation.ErrorMessage = “Please select a Provider Group Code from the list.”;
//Specify the area i.e. B2:B32
area.StartRow = 1;
area.EndRow = 31;
area.StartColumn = 1;
area.EndColumn = 1;
Note: Please try using our latest version/fix v8.0.1.x (as Babar recommended), it will work fine.