How do I conduct conditional data validation multiple times so that each input prompts a different conditional validation outcome

Question

I am a member of a project that does electricity monitoring, and I am trying to organize our contact sheet to indicate the status of our electricity monitoring across locations. I want to make the data sheet as simple as possible, so I am determined to using conditional data validation, which gives drop down answers you can choose from, based on previous answers.

I created a decision tree to help outline the data validation steps I want the conditional validation to follow.

I have very little experience with Google Sheets, however I feel as if I have somewhat successfully achieved my goal. Please view the following link and test it by selecting an option in Cell B7, and then proceed by choosing further data validation options in cells N7:P7.

https://docs.google.com/spreadsheets/d/1kCfP1zDkKXoGMys6xAQRcl_TaVCoHPDbAOIJ8zzDaDA/edit?usp=sharing

While it works in that isolated context, I want to fix the code so that I can choose a different option for each cell in column B, and follow the data validation steps independently from the other rows. At the moment, I just have the first cell (B7) equaling the M7 cell, however I ultimately want it so that any cell that I choose in column B, updates the values in M7. I plan on pasting the results in the three columns under the status header (columns C, D, and E).

In addition to these changes, I would also like that if the status box response is "awaiting response" that no more data validation responses are accepted for the row.

This would be a lifesaver if you could assist with this. I am sorry if it is not completely clear what I am describing, however I think if you use the demo you might understand the problem I'm having.

Thank you so much!

Answer 1

The concept you are describing is known as multiple-level dependent drop-down lists.

Single-level dependent drop-down lists can be implemented with named ranges, but when multiple levels of drop-downs are required, the whole thing is usually simpler to implement with a script.

See the dependentDropDownLists_ script for one example. You can try it out with these parameters:

    // [START modifiable parameters]
    const parameters = [
      {
        sheets: /^(Sheet1)$/i,
        rangesA1: ['B2:E',],
        definitionRangesA1: ['DropDownDefinitions!A2:D',],
      },
    ];
    // [END modifiable parameters]

The script requires that the drop-downs are defined through a row-oriented table like the one I inserted in the 'DropDownDefinitions' tab in your sample spreadsheet.