Google Sheets – How to Dynamically Populate a Dropdown Based on Other Cells

See this example spreadsheet. Sheet Countries contains source data (instead of prices and products, I've used countries, population and other data, but the concept is the same). Sheet Dropdown contains the example. In that latter sheet, cell C1 and E1 are dropdown menus (use the Data→Validation… menu to see how data validation works, or look at the explanations at the bottom of this answer).

Then, in cell A2, you have a query that fetches data from the Countries sheet, based on the values present in your dropdown menus. The trick is to build the query string using the string concatenation operator (&). What the query string says is: fetch data from columns A to E of the Countries sheet, where column C is lower than Dropdown!C1 and column E is lower than Dropdown!E1.

Note that you will need to copy the example spreadsheet to play with it (you can only comment the original).

Explanations To set a cell as a drop down menu, select the cell, then use the Data→Validation… menu. For instance, for cell C1 of the Dropdown sheet, you can enter the list of values you want users to select from by setting the Criteria to List of items and then enter comma separated values in the input box right next to it. Other options of this validation menu are self explanatory.

Another common dropdown menu is where you want users to select values from a range within the spreadsheet: As you can see here, the Criteria is now set to List from a range. You enter the range itself by clicking on the grid within the input box, and then by selecting the range of cells you want to use as your selectable items. In the case of our examples, the values come from the sheet Population range where I made some computations to generate a log scale list of values between the smallest country population and the largest.

Hope this helps.

This can be done with Google Script. An example is here.

function onEdit(e) {

var ss = e.source;
var s = ss.getSheetByName("Testing");
var r = e.range;

  // to let you modify where the action and move columns are in the form responses sheet
var actionCol = 1;

  // Get the row and column of the active cell.
var rowIndex = r.getRowIndex();
var colIndex = r.getColumnIndex();
  // Get the number of columns in the active sheet.
  // -1 to drop our action/status column
  //var colNumber = s.getLastColumn()-1; //not needed because we are limiting the edit to a single row and cell

var curCell = s.getRange(rowIndex,1, 1, 1); //the same cell that has dropdown but can be a different one

if (e.value !== "" && colIndex == actionCol) {
var curCellValue =  curCell.getValue();
var curCellLeft = curCellValue.split(" ");
var val = curCellLeft[0];
//is it a number?
var matches = val.match(/\d+/g);
if (matches != null) {
 curCell.setValue(val);
}
}
}