You can create ordinary dropdown list in Google Sheet. But, how about creating sub-category dropdown list? Google Sheet does not provide capability to do this. You have to create it by your own.
So, in this tutorial, I will guide you on how to create nested dropdown list like image below:
Credit: This is an improved version of Max Makhrov which fix bug when there is same sub-category name across parent categories.
To start, you have to create sheet contain category data.
1Click "Add Sheet" on lower left corner of window.
12Go back to first sheet.
13Select the cell you want to create dropdown list.
14Right click and click "Data validation ...".
15Click "Select data range" button.
16Then select the first column data of your category sheet, and click "OK".
17The result will be like this. Click "Save".
18The dropdown list will be generated.
At this time, you can only select the first category. Next step will guide you on how to created dynamic dropdown list.
19Click "Tools" => "Script editor ...".
20Delete all default code in the file.
21Paste this code
22In function onEdit(event), edit the variables as follows:
23Click "File" => "Save" button.
24You can name the file to be anything you want. Click "OK".
25Now everything is setup! Go back to first sheet, you can now select drop down list and see that sub-category dropdown list is generated.
Note: If you want to add more category data. Make sure that category data is sorted (see step 7 - 11).
Happy Coding!
function SmartDataValidation(event, TargetSheet, LogSheet, NumOfLevels, lcol, lrow)
{
//--------------------------------------------------------------------------------------
// The event handler, adds data validation for the input parameters
//--------------------------------------------------------------------------------------
// =================================== key variables =================================
//
// ss sheet we change (TargetSheet)
// br range to change
// scol number of column to edit
// srow number of row to edit
// CurrentLevel level of drop-down, which we change
// HeadLevel main level
// r current cell, which was changed by user
// X number of levels could be checked on the right
//
// ls Data sheet (LogSheet)
//
// ======================================================================================
// [ 01 ].Track sheet on which an event occurs
var ts = event.source.getActiveSheet();
var sname = ts.getName();
if (sname == TargetSheet)
{
// ss -- is the current book
var ss = SpreadsheetApp.getActiveSpreadsheet();
// [ 02 ]. If the sheet name is the same, you do business...
var ls = ss.getSheetByName(LogSheet); // data sheet
// [ 03 ]. Determine the level
//-------------- The changing sheet --------------------------------
var br = event.source.getActiveRange();
var scol = br.getColumn(); // the column number in which the change is made
var srow = br.getRow() // line number in which the change is made
// Test if column fits
if (scol >= lcol)
{
// Test if row fits
if (srow >= lrow)
{
var CurrentLevel = scol-lcol+2;
// adjust the level to size of
// range that was changed
var ColNum = br.getLastColumn() - scol + 1;
CurrentLevel = CurrentLevel + ColNum - 1;
// also need to adjust the range 'br'
if (ColNum > 1)
{
br = br.offset(0,ColNum-1);
} // wide range
var HeadLevel = CurrentLevel - 1; // main level
// split rows
var RowNum = br.getLastRow() - srow + 1;
var X = NumOfLevels - CurrentLevel + 1;
// the current level should not exceed the number of levels, or
// we go beyond the desired range
if (CurrentLevel <= NumOfLevels )
{
// determine columns on the sheet "Data"
var KudaCol = NumOfLevels + 3;
var KudaNado = ls.getRange(1, KudaCol);
var lastRow = ls.getLastRow(); // get the address of the last cell
var ChtoNado = ls.getRange(1, KudaCol, lastRow, KudaCol);
// ============================================================================= > loop >
for (var j = 1; j <= RowNum; j++)
{
for (var k = 1; k <= X; k++)
{
HeadLevel = HeadLevel + k - 1; // adjust parent level
CurrentLevel = CurrentLevel + k - 1; // adjust current level
var r = br.getCell(j,1).offset(0,k-1,1);
var SearchText = r.getValue(); // searched text
// if anything is choosen!
if (SearchText != '')
{
//(Piyapan added) get first row number that match the category
var code = "2";
var rowNumberCol = NumOfLevels + 2;
for (var i = 1; i < CurrentLevel; i++)
{
var selectCategory = ts.getRange(srow+j-1, lcol+i-1, srow+j-1, lcol+i-1).getCell(1,1).getValue();
code = 'VLOOKUP("' + selectCategory + '",INDIRECT("R" & (' + code + ') & "C' + i + ':R' + lastRow + 'C' + rowNumberCol +'",0), '+ (rowNumberCol-i+1) +', FALSE)';
}
code = 'IFERROR(' + code + ', 0)';
KudaNado.setFormulaR1C1(code);
var startRowNumber = ChtoNado.getCell(1,1).getValue();
if(startRowNumber > 0)
{
var categoryRange = ls.getRange(1, CurrentLevel-1, lastRow, CurrentLevel-1);
var firstValue = categoryRange.getCell(startRowNumber,1).getValue();
var categoryCount = 1;
for (var i = startRowNumber+1; i <= lastRow; i++)
{
var value = categoryRange.getCell(i,1).getValue();
if(firstValue != value)
break;
categoryCount++;
}
// Piyapan the formula
code = 'UNIQUE(INDIRECT("R' + startRowNumber + 'C' + CurrentLevel + ':R'+ (categoryCount+startRowNumber-1) + 'C' + CurrentLevel+'",0))';
//-------------------------------------------------------------------
// [ 04 ]. define variables to costumize data
// for future data validation
//--------------- Sheet with data --------------------------
// combine formula
// repetitive parts
//var IndCodePart = 'INDIRECT("R1C' + HeadLevel + ':R' + lastRow + 'C';
//IndCodePart = IndCodePart + HeadLevel + '",0)';
// the formula
// code = '=UNIQUE(INDIRECT("R" & MATCH("';
// code = code + SearchText + '",';
// code = code + IndCodePart;
// code = code + ',0) & "C" & "' + CurrentLevel
// code = code + '" & ":" & "R" & COUNTIF(';
// code = code + IndCodePart;
// code = code + ',"' + SearchText + '") + MATCH("';
// code = code + SearchText + '";';
// code = code + IndCodePart;
// code = code + ',0) - 1';
// code = code + '& "C" & "' ;
// code = code + CurrentLevel + '",0))';
// Got it! Now we have to paste formula
KudaNado.setFormulaR1C1(code);
// get required array
var values = [];
for (var i = 1; i <= lastRow; i++)
{
var currentValue = ChtoNado.getCell(i,1).getValue();
if (currentValue != '')
{
values.push(currentValue);
}
else
{
var Variants = i-1; // number of possible values
i = lastRow; // exit loop
}
}
//-------------------------------------------------------------------
// [ 05 ]. Build daya validation rule
var cell = r.offset(0,1);
var rule = SpreadsheetApp
.newDataValidation()
.requireValueInList(values, true)
.setAllowInvalid(true)
.build();
cell.setDataValidation(rule);
if (Variants == 1)
{
cell.setValue(KudaNado.getValue());
} // the only value
else
{
k = X+1;
} // stop the loop through columns
}
else
{
// kill extra data validation if there were
// columns on the right
if (CurrentLevel <= NumOfLevels )
{
for (var i = 1; i <= NumOfLevels; i++)
{
var cell = r.offset(0,i);
// clean
//cell.clear({contentsOnly: true});
// get rid of validation
cell.clear({validationsOnly: true});
}
} // correct level
} // empty row
} // not blanc cell
else
{
// kill extra data validation if there were
// columns on the right
if (CurrentLevel <= NumOfLevels )
{
for (var i = 1; i <= NumOfLevels; i++)
{
var cell = r.offset(0,i);
// clean
//cell.clear({contentsOnly: true});
// get rid of validation
cell.clear({validationsOnly: true});
}
} // correct level
} // empty row
} // loop by cols
} // loop by rows
// ============================================================================= < loop <
} // wrong level
} // rows
} // columns...
} // main sheet
}
function isNumber(n) {
return !isNaN(parseFloat(n)) && isFinite(n);
}
function removeDuplicates() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var newData = new Array();
for(i in data){
var row = data[i];
var duplicate = false;
for(j in newData){
if(row.join() == newData[j].join()){
duplicate = true;
}
}
if(!duplicate){
newData.push(row);
}
}
sheet.clearContents();
sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}
function onEdit(event)
{
// Declare some variables:
//--------------------------------------------------------------------------------------
var TargetSheet = 'Sheet1' // name of the sheet where you want to verify the data
var LogSheet = 'Category' // name of the sheet with information
var NumOfLevels = 3 // number of levels in LogSheet
var lcol = 2; // number of the leftmost column in TargetSheet, in which the changes are checked; A = 1, B = 2, etc.
var lrow = 4; // line number in TargetSheet from which the rule will be valid
//--------------------------------------------------------------------------------------
SmartDataValidation(event, TargetSheet, LogSheet, NumOfLevels, lcol, lrow);
}
- Target Sheet: Name of the sheet where you want to verify the data.
- LogSheet: Name of the sheet with information.
- NumOfLevels: Number of levels in LogSheet.
- lcol: Number of the leftmost column in TargetSheet, in which dropdown list will applied; (A = 1, B = 2, etc.).
- lrow: Line number in TargetSheet from which dropdown list will applied.
Happy Coding!
How can I insert an offset between the first dropdown and the next ones (have a few columns betwee)
ReplyDeleteCan we utilize this in multiple sheets within the same worksheet?
ReplyDelete