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.
Attempts Made
https://docs.google.com/spreadsheets/d/1KIgP7u9yS_w3DjBnXwHUkHuqLaR9zmdlaRbbOWt1q3I/edit?usp=sharing
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.
3
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:
Reference:
Data Validation
1