4

I want to populate a dropdown list based on a selection of another cell's dropdown list.

Here is my spreadsheet.

E.g., if the user does not make a choice from D20 dropdown menu, therefore F20 dropdown is Blank but has the option of only ChoiceA in its dropdown menu.

If User does make a choice from the D20 drop-menu, therefore F20 is Blank but has options of ChoiceA, ChoiceB, and ChoiceC in its dropdown menu.

I've played around with the below but it's not quite right...

This formula is in cell Sheet2!B:14

=ARRAYFORMULA(IF(ISBLANK(Sheet1!D20),(Sheet2!A14),(Sheet2!A14:A16)))

and my Sheet1!F20 cell uses data validation list from range Sheet2!B14:B16

On a single line it works as needed, but when rows are added issue that arose is once a selection is made anywhere in column D that allows for all 3 choices in the F column drop-menu, then all 3 choices become permanently available in any added F column cells regardless of column D choice in the same row.

Another issue needing a fix is if the option in Column D is deleted, all three options are also still available - although a warning comment is present.

Can these issues be solved either with formula or script?

1

If you need multiple validation rules for multiple cells, you should also have multiple ranges for them, so that the rules are tracked independently of each other.

But the second issue, with an illegal choice staying in F after an edit to D, can't be solved with formulas alone. So I wrote a script that keeps better track of validation in column F. Note that it does not set the initial validation rules (before first use); that you can do manually. It also doesn't set rules for column D, which are static.

What it does is the following: when a cell in column D is edited (header row excluded), it adjusts the validation rule of the cell two steps to the right accordingly. Additionally, if that cell contained a value such as OptionB that is no longer allowed when D is blank, the value in F is removed.

In the script, e.range is the cell edited, e.value is its new value.

function onEdit(e) {
  if (e.range.getColumn() === 4 && e.range.getRow() > 1 ) {  // only Column D, not header 
    var optionA = SpreadsheetApp.newDataValidation()
      .requireValueInList(['OptionA'], true).build();
    var optionsABC = SpreadsheetApp.newDataValidation()
      .requireValueInList(['OptionA', 'OptionB', 'OptionC'], true).build();

    var affectedCell = e.range.offset(0,2,1,1);  // two steps to the right
    if (e.value === '') {
      if (affectedCell.getValue() !== 'OptionA') {
        affectedCell.setValue('');    // reset if current choice is no longer legal
      }
      affectedCell.setDataValidation(optionA); 
    }
    else {
      affectedCell.setDataValidation(optionsABC);      
    }
  }
}
  • Normal, have pasted into script editor and adjusted to my 3 options. I still get all options available in any added rows after the initial run (ie after 1st edit of column D). The script overwrites my manual validation rule of column F " List of Items: OptionA". Suggestions? – MJM Nov 19 '15 at 5:17
  • How are rows added? – user79865 Nov 19 '15 at 5:19
  • with built in add button on the goggle sheet – MJM Nov 19 '15 at 5:28
  • can send sheet link – MJM Nov 19 '15 at 5:38
1

here is a simple formula solution:

Sheet1:

D20: Data Validation > List of items: I made a choice,I didn't make a choice

F20: Data Validation > List from a range: Sheet2!D8:D11

Sheet2:

D8: =IF(Sheet1!D20 = B6; QUERY(B8:B11;;-1);IF(Sheet1!D20 = C6; QUERY(C8:C11;;-1);))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.