Search code examples
validationgoogle-sheetsgoogle-sheets-formulaconditional-formatting

Conditional Dropdowns in Google Sheets Based on Multiple Criteria

I’m having trouble setting up conditional drop-downs in Google Sheets based on specific criteria. Here’s what I’m trying to achieve and the issues I’ve encountered:

Objective

I want to create a drop-down list in a column if the following conditions are met:

- Condition 01: The value in column E of the same row should be one of these specified values:

T1, T2, T3, T4, T5, T6, P1, P2, P3, B1, or B2.

- Condition 2:

The drop-down options should be sourced from the sheet 'SEO Validación Datos'. Specifically, the data should come from the column starting at row 6 in 'SEO Validación Datos', but only if the value in row 2 of that sheet is "HOME".

To finish off, I would like to use conditional formatting to show the drop-down options in Red if the value in column C in sheet 'SEO' equals Principal and Blue if the value equals Secundaria. There might be some changes required in the SEO Validación sheet, but I don't know how to progress at this point.

enter image description here

Attempts Made

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

enter image description here

Data Validation Setup

I attempted to use the Data Validation feature to create drop-downs based on the values the sheet 'SEO Validación Datos'. I was able to set up drop-downs but:

  • I could not link the drop-down list dynamically based on the conditions from column E. When I try to add the second rule the first one disappears altogether.
  • I was also unable to create a condition for the value 'Home' row 2 of the sheet 'SEO Validación Datos'

Using Formula to Display Values

I tried using the formula

=IF(OR(E3="T1", E3="T2", E3="T3", E3="T4", E3="T5", E3="T6", E3="P1", E3="P2", E3="P3", E3="B1", E3="B2"), G3, "")

in a helper column to display values conditionally.

However, this approach only displays values and does not apply drop-down functionality.

Dynamic Dropdown Application

I tried combining REGEXMATCH with other formulas to conditionally show drop-down options but faced difficulties getting the drop-down to appear based on dynamic conditions.

Request

Can anyone provide a solution or guide on how to achieve this in Google Sheets?

Under the sheet 'Ideal result - Home' there is a preview of what I am hoping to achieve.

enter image description here

Solution

  • Creating Dynamic Dropdowns Referencing values from other Sheets

    It seems that there are some issues with getting the correct ranges. So for this one I would suggest that you delete currently existing Data Validation on the Sheet Range you are trying to work with to avoid overlapping over validation.

    Sample Code:

    function applyDropdown(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var homeSheet = ss.getSheetByName("Expected Result - Home")
var vsSheetData = ss.getSheetByName("Validación Scripts").getRange("A4:A8");
var columnH = homeSheet.getRange(4,5,homeSheet.getLastRow(), 1);
var columnHValues = columnH.getValues().flat();
var validValues = ['T1', 'T2', 'T3', 'T4', 'T5', 'T6', 'P1', 'P2', 'P3', 'B1', 'B2']
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(vsSheetData).build();
for(x = 0; x <columnHValues.length; x++){
if(columnHValues[x] != "" && validValues.includes(columnHValues[x])){
  homeSheet.getRange(x+4, 8).setDataValidation(rule);
  }

}

}

    Sample Output:

    Sample Output

    Reference:

    Data Validation