How to make a data validation drop down menu that offers a set of options based on the previous cell's selection in Sheets?

Column F has a dropdown menu of 4 options: A, B, C, D. I would like column G to offer a dropdown of options that changes depending on the option chosen in column F.

For example: if A is chosen, Column G options are 1, 2, or 3; if B is chosen, options are 12, 20, 22, 45, 84, and 90; if C is chosen, options are French or Spanish. Etc.

Is this possible?

I tried creating a separate tab with all the options grouped separately. I thought I might be able to insert an IF formula as the criteria in the data validation rules for column G. I put all the options from column F as headers in a separate tab, then put all the options I want to see in column G under each header. But I'm not very good with IF, AND, OR formulas and I'm not sure these even can apply because they are looking at true vs false and I need something to look at "which of these is true, then do X"

Here's a simplified version of the formula I tried:

=IF(OR(F3=A1, F3=B1, F3=C1, F3=D1) A2:A7, B2:B14, C2:C9, D2:D11)

Solution

ALTERNATIVE SOLUTION

This particular scenario that you want to achieve — a dynamic data validation rule, can be done using Google Apps Script. To access the script editor directly from your spreadsheet, you can click on Extensions > Apps Script.

Now, some key points on how we will approach this using Apps Script:

The main structure of our script would be using the onEdit() trigger so that you will not need to run the script every time a change is made. On the contrary, this trigger will allow you to make changes on the initial drop-down list, and the script will be triggered automatically, applying those said changes onto the dynamic drop-down.
To explain how the script works, what happens is it will access the data from a different tab, which will then be used as the source for the dynamic data validation that will be created. Since you mentioned that the actual data that you will be using is already located on a separate sheet, the script should also be able to work the same with the real dataset.
The main operation of the data validation creation is through the while() loop, as it will go through all the values on the initial drop-down list, and then create the dynamic drop-down with the corresponding values using the newDataValidation() function.

As for the script itself, you can use this sample script for your reference to see how it works:

// This function takes effect whenever an edit is made on the sheet
// For this case, it is whenever the 1st set of drop-down lists are changed (e.g. the headers)
// However, it will not apply to already existing drop-down lists, and it will take effect
// as soon as an edit is made, where it will create the dynamic drop-down list according
// to the headers drop-down

function onEdit(e) {
  var editRange = e.range;    // The range where the edits are expected to be made
  var ss = editRange.getSheet();
  var dataValidation = "Sheet12";  // Sheet where the data validation is applied
  // This if statement specifically checks the range wherein the dynamic data validation rule will be applied
  if (editRange.getColumn() == 6 && editRange.getRow() > 1 && ss.getSheetName() == dataValidation) {
    var source = "Data";  // Sheet where the source data is located
    var sourceList = e.source.getSheetByName(source);
    var makes = sourceList.getRange(1, 1, 1, sourceList.getLastColumn()).getValues(); 
    var activeCell = editRange.getCell(1, 1); // Start with the first cell
    var remRows = editRange.getHeight();      // Gets the number of rows of the edit range
    // This loop will apply all the expected values corresponding to the assigned header value
    // on the previous drop-down list
    while (remRows > 0) {
      var cellValue = activeCell.getValue();
      activeCell.offset(0, 1).clearContent().clearDataValidations(); // Always clear content & validations
      if (cellValue != "") { // Add validations if cell isn't blank
        var makeIndex = makes[0].indexOf(cellValue) + 1;
        if (makeIndex != 0) {
          // this gets the range of the items that will be placed on the created validation rule
          var validationRange = sourceList.getRange(3, makeIndex, sourceList.getLastRow() - 2);
          // this sets the list of items to be placed on the created validation rule
          var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
          activeCell.offset(0, 1).setDataValidation(validationRule);
        }
      }
      activeCell = activeCell.offset(1, 0); // Get the next cell down
      remRows--; // Decrement the counter
    }
  }
}

Once the script is saved, you can refresh your spreadsheet and the script should take effect once you apply any changes on the drop-down lists.

OUTPUT

You can also click on the following links for your reference: