Depending dropdown for each row in excel using data validation formula

Aspose.Cells Product Family
1

we are using dynamic aspose excel. in that excel we using dropdown depending on selection. ex - if i select api from one dropdown on the next row api data will show. in the above code i can able to achieve for the B$3$ but need to apply to 50 rows. ex- from $B$3 to $B$50. how can i achieve that in aspose

ValidationCollection validationsAM2 = FirstSheet.Validations;
// Create Cell Area
CellArea caam2 = new CellArea();
caam2.StartRow = 3;
caam2.EndRow = 19;
caam2.StartColumn = 2;
caam2.EndColumn = 2; // Create a new validation to the validations list.
Validation validationam2 = validationsAM2[validationsAM2.Add(caam2)]; // Set the validation type.
validationam2.Type = Aspose.Cells.ValidationType.List; // Set the operator.
validationam2.Operator = OperatorType.None; // Set the in cell drop down.
validationam2.InCellDropDown = true; // Set the formula1.
validationam2.Formula1 = @"=INDIRECT($B$3)"; // Enable it to show error.
validationam2.ShowError = true; // Set the alert type severity level.
validationam2.AlertStyle = ValidationAlertType.Stop; // Set the error title.
validationam2.AddArea(caam2);

2

@sam17031995,

Could you please perform your desired task in MS Excel manually, save the file, and provide it to us (please zip the file prior to attaching) to demonstrate your requirements? We will check and help you on how to do it via Aspose.Cells APIs.

3

Thk you for your quick resoponse.

step one
Worksheet FirstSheet = workbook.Worksheets[0];
FirstSheet.Name = “Analytical Method”;
workbook.Worksheets.Add(“Analytical Method DropDown Data”);
workbook.Worksheets.Add(“Material of Construction detail”);
Worksheet DrodownSheet = workbook.Worksheets[1];
Worksheet MOCSheet = workbook.Worksheets[2];
step two - setting column name
FirstSheet.Cells[0, 0].Value = Localizer.customText(“lbl_AnalyticalMethodName”);
FirstSheet.Cells[0, 1].Value = Localizer.customText(“lbl_AnalyticalMethodfor”);
FirstSheet.Cells[0, 2].Value = Localizer.customText(“lbl_select”);
step three - Binding the data
step four - Apply the conditionParis-AnalyticalMethodTemplate (68).7z (12.2 KB)

ValidationCollection validationsAM2 = FirstSheet.Validations;
// Create Cell Area
CellArea caam2 = new CellArea();
caam2.StartRow = 2;
caam2.EndRow = 19;
caam2.StartColumn = 2;
caam2.EndColumn = 2; // Create a new validation to the validations list.
Validation validationam2 = validationsAM2[validationsAM2.Add(caam2)]; // Set the validation type.
validationam2.Type = Aspose.Cells.ValidationType.List; // Set the operator.
validationam2.Operator = OperatorType.None; // Set the in cell drop down.
validationam2.InCellDropDown = true; // Set the formula1.
validationam2.Formula1 = @"=INDIRECT(SUBSTITUTE($B$3,"" “”,""_""))"; // Enable it to show error.
validationam2.ShowError = true; // Set the alert type severity level.
validationam2.AlertStyle = ValidationAlertType.Stop; // Set the error title.
validationam2.AddArea(caam2);

step to reproduce

  1. download or extract the excel from zip
  2. open the excel in the analytical method sheet where one column call analytical method for
  3. if i select any value from that column - nxt column select will show value depends upon the selection
  4. i used validationam2.Formula1 = @"=INDIRECT(SUBSTITUTE($B$3,"" “”,""_""))";
    for that 3rd row so it is only apply on B3.
  5. if i select the analytical method for the B3 - b4,b5 every row is bind the same value on the selection
  6. But i want every cell data will show depends on the same row.

i have attached the excel with the functionality
thanks in advance

4

@sam17031995,

I checked your sample Excel file. We requested you to kindly provide the sample Excel file which contain the cascaded (dependent) drop downs that should work properly and accordingly for every list items in alternative columns. I think you have not provided an Excel file containing your desired dependent dropdown lists (data validation) in tact that work fine. We suggest you should first google/browse internet on how to create dependent (cascading) drop-down lists in MS Excel then devise the technique based on your data for your scenario/case. Once you are successful to achieve your desired results in MS Excel manually, then you can write sample code via Aspose.Cells APIs to accomplish the exact task accordingly.