Selecting chapters, section and subsection with google sheets with drop down list

Question

we have currently a large number of exercises (for math) that need to be categorized according to an existing table of contents. Each exercise has a unique number. This number should be placed into the table of contents. The depth of the toc is 3, so we have chapters, sections and subsections.

I want three drop down lists for each of the (many) exercises. The first one selects the chapter, the second the section, and the third the subsection. I can solve this with two filtered lists that depend on certain filtering of a list seperated into chapter, section, subsection. That works fine for few exercises. But, i have to make the filtered lists for each exercise seperately. That is exactly the problem. I do not want to maintain 500 lists (two per exercise)

Is there any way to hardcode this? I do not want to scroll through the many subsections each time and I need a error control. So, one should not be able to select chapter 3 and then subsection 3 from chapter 4.

EDIT: Link to Google Sheet: https://docs.google.com/spreadsheets/d/e/2PACX-1vSsRqFlLIkpgIrw18GBDLdUEl0FFmF5hSXIe2oAXztz9N50VNiO0eCP3cAB20KOgHU4nfH6gBFCWmyT/pubhtml

Answer 1

1. Separate the data and the dropdows

That's a best practice, because you will need to have some auxiliary cells to create the dependency

2. Create a simple data validation dropdown

Do it for the first column in a separate sheet

3. Create the auxiliary cells

Filter the second column with the values you have in the data validation to show the values adjacent to the first column

=FILTER(B2:B,A2:A=Dropdowns!$A$2)

4. Repeat the process with as many columns you have

Here is the sample spreadsheet for you to have an idea how to implement it https://docs.google.com/spreadsheets/d/1VPsx1hfKuZDifMgrREOvUb4xEIvbVODnKPuRDWZUOF8/edit?usp=sharing

Update

I'm working on a script, I created two functions, at the moment they're not achieving the step you want, but I'm thinking on looping the number of columns as the same way I'm doing it with the rows.

const menuSheet = 'Values';
const dataSheet = 'Data';
const wsValues = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(menuSheet);
const wsData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSheet);
const data = wsData.getRange(2, 1, wsData.getLastRow() - 1, 3).getValues();
let firstCol = 1;
let secondCol = 2;
let thirdCol = 3;

/*
function setDropdownDependenciesTest() {
  const list = ['a', 'b']
  const cell = wsValues.getRange('C2')

  setDropdownDependencies(list, cell)

}
*/

function onEdit(e) {
  const activeCell = e.range
  let val = activeCell.getValue()
  let row = activeCell.getRow()
  let column = activeCell.getColumn()
  let wsName = activeCell.getSheet().getName()
  if (wsName === menuSheet && column === firstCol && row > 1) {
    applyFirstValidation(val, row)
  } else if (wsName == menuSheet && column === secondCol && row > 1) {
    applySecondValidation(val, row)
  }
}

function applyFirstValidation(val, row) {
  if (val === "") {
    wsValues.getRange(row, secondCol).clearContent()
    wsValues.getRange(row, secondCol).clearDataValidations()
    wsValues.getRange(row, thirdCol).clearContent()
    wsValues.getRange(row, thirdCol).clearDataValidations()
  } else {
    wsValues.getRange(row, secondCol).clearContent()
    wsValues.getRange(row, secondCol).clearDataValidations()
    wsValues.getRange(row, thirdCol).clearContent()
    wsValues.getRange(row, thirdCol).clearDataValidations()
    let filteredData = data.filter(info => {
      return info[0] === val;
    })

    let listToApply = filteredData.map(info => {
      return info[1]
    })

    let cell = wsValues.getRange(row, secondCol)
    setDropdownDependencies(listToApply, cell)
  }
}

function applySecondValidation(val, row) {
  if (val === "") {
    wsValues.getRange(row, thirdCol).clearContent()
    wsValues.getRange(row, thirdCol).clearDataValidations()
  } else {
    wsValues.getRange(row, thirdCol).clearContent()
    let firstColValue = wsValues.getRange(row, firstCol).getValue()
    let filteredData = data.filter(info => {
      return info[0] === firstColValue && info[1] === val;
    })

    let listToApply = filteredData.map(info => {
      return info[2]
    })

    let cell = wsValues.getRange(row, thirdCol)
    setDropdownDependencies(listToApply, cell)
  }
}

function setDropdownDependencies(list, cell) {

  const rule = SpreadsheetApp
    .newDataValidation()
    .requireValueInList(list)
    .setAllowInvalid(false)
    .build()

  cell.setDataValidation(rule)
}