Validation from list when other cell populated

1 Answer 12 Views
Spreadsheet
Nigel
Top achievements
Rank 1
Nigel asked on 06 Jan 2022, 02:43 PM

Hi guys,

I'm able to perform cell validation by providing a list of acceptable values. This provides the user with a dropdown to select a suitable value. This is great, but I need to extend it so that it is mandatory when the value in another cell is provided, but must be blank if the other cell is not populated. For example:

  • Cell A1 can take any value, including blank
  • When cell A1 is blank, cell B1 must be blank
  • When cell A1 is not blank, cell B1 must be populated from its dropdown list

Is it possible to achieve this?

1 Answer, 1 is accepted

Sort by
0
Neli
Telerik team
answered on 11 Jan 2022, 11:11 AM

Hello Nigel,

I would suggest you subscribe to the Spreadsheet select event. When the selection is changed you could check the value of the needed cell. Then, you could use the Range validation method to set the needed validation or you can clear the cell using the clear method. Below is an example.

select: function(e){
    var current = e.range;
    var sheet = e.sender.activeSheet();
    var val = sheet.range("A1").value();
    if(val !== null){  
      sheet.range("B1").value('Select');
      sheet.range("B1").validation({
        dataType: "list",
        showButton: true,
        comparerType: "list",
        from: '"Foo item 1,Bar item 2,Bar item 3"',
        allowNulls: true,
        type: "reject"
      });
    }else{
    	sheet.range("B1").clear();
    }
  }

Here is a Dojo example where this is demonstrated.

Regards,
Neli
Progress Telerik

Love the Telerik and Kendo UI products and believe more people should try them? Invite a fellow developer to become a Progress customer and each of you can get a $50 Amazon gift voucher.

Tags
Spreadsheet
Asked by
Nigel
Top achievements
Rank 1
Answers by
Neli
Telerik team
Share this question
or