Prepare a validation dropdown based on conditional data

Aspose.Cells Product Family
1

Hi Team,

I am trying to create a conditional dropdown based on values of other cells.
For example in the attached file, there are two sheets TestData and Master Data

In the test data value in the column choose needs to populated as list validation with the values in the dropdown based on the following logic.

For a combination of Level1 and Level2 in master data get all values of level3 except the rows own level3 value.

For example for the combination One and A in level1 and level2 column resp., the possible values in master data are 1,2,3 but since in the test data for Row number 2, 1 is already present , so the dropdown should have values 2 or 3.

How can this be achieved using aspose? Please help.

Please helpAsposeIssue.zip (6.9 KB)

2

@siddhanntarora1992,

Thanks for the MS Excel file and details.

Please perform your task and create such (dependent) Excel drop down list (data validations) in MS Excel manually, save the file and provide us here to demonstrate your requirements, we will check and
help you on how to do it via Aspose.Cells APIs.

3

Hi @Amjad_Sahi,

I have achieved this using excel, please find attached.
Also applied the data validation in the “Choose” column in excel.

But the problem i am facing now is i see a dropdown but the values doesn’t populate unless i click on data validation, do nothing and HIT ok, as i have done already for row1 but for other rows the drop down is still empty.

If i click on data validation and then OK the values will come, please help why is this happening.

i am using the below code for setting validation
//Get the validations collection
var validations = WorkSheet.Validations;

        // Specify the validation area
        CellArea area;
        area.StartRow = 1;
        area.EndRow = MaxRowCount == null ? 100 : MaxRowCount.Value;
        area.StartColumn = ColumnIndex;
        area.EndColumn = ColumnIndex;

        //Create a new validation to the validations list
        var validation = validations[validations.Add(area)];

        //Set the validation type
        validation.Type = ValidationType.List;

        //Set the operator
        validation.Operator = OperatorType.Between;

        //Set the in cell drop down
        validation.InCellDropDown = true;

        //Set the formula1
        validation.Formula1 = formula;

        //Enable it to show error, you can turn it ON or OFF as desired
        validation.ShowError = true;

        //Set the alert type severity level
        validation.AlertStyle = ValidationAlertType.Stop;

        // Set the error title
        validation.ErrorTitle = ErrorTitle;

        //Set the error message
        validation.ErrorMessage = ErrorMessage;

<a class=“attachment” href="/uploads/default/34138">Aspose_Data_Validation_Issue.zip (8.0 KB)Aspose_Data_Validation_Issue.zip (8.0 KB)

4

@siddhanntarora1992,

Thanks for the sample code segment and template file.

I extracted the file from your attached zipped archive. I opened your file into Ms Excel manually and noticed the behavior ("…i see a dropdown but the values doesn’t populate unless i click on data validation, do nothing and HIT") for Choose column in the first worksheet as you pointed out. I also checked your code segment but I am not sure for certain objects/variables in it. We require the following to evaluate your issue precisely, so do the needful:

  1. A complete runnable sample code or preferably a stand-alone console application (you may zip the project prior attaching) which could be used to generate the output file and to reproduce the issue.

  2. Your expected Excel file as you have already achieved your task in MS Excel manually.

  3. Your input file (if any).

Once we have the above artifacts, we will start working over your issue soon.