To convert YYYY, MM, DD to an actual Date object, you need the DATE command. The following works:
=filter(A6:F371,A6:A371>=DATE(2015,1,1),A6:A371<=DATE(2015,1,31))
But I see you want to filter based on dropdown entry. For this I would use MONTH function. For example, suppose cell J10 has validation rule: list of items
01 (January), 02 (February), 03 (March), and so on
Then your filter in J11 could be
=filter(A6:F371,MONTH(A6:A371)=VALUE(LEFT(J10,2)))
The idea here is that the names of month help the user in selection, but the filter uses the numeric format (month number).
If you need multiple validation rules for multiple cells, you should also have multiple ranges for them, so that the rules are tracked independently of each other.
But the second issue, with an illegal choice staying in F after an edit to D, can't be solved with formulas alone. So I wrote a script that keeps better track of validation in column F. Note that it does not set the initial validation rules (before first use); that you can do manually. It also doesn't set rules for column D, which are static.
What it does is the following: when a cell in column D is edited (header row excluded), it adjusts the validation rule of the cell two steps to the right accordingly. Additionally, if that cell contained a value such as OptionB that is no longer allowed when D is blank, the value in F is removed.
In the script, e.range is the cell edited, e.value is its new value.
function onEdit(e) {
if (e.range.getColumn() === 4 && e.range.getRow() > 1 ) { // only Column D, not header
var optionA = SpreadsheetApp.newDataValidation()
.requireValueInList(['OptionA'], true).build();
var optionsABC = SpreadsheetApp.newDataValidation()
.requireValueInList(['OptionA', 'OptionB', 'OptionC'], true).build();
var affectedCell = e.range.offset(0,2,1,1); // two steps to the right
if (e.value === '') {
if (affectedCell.getValue() !== 'OptionA') {
affectedCell.setValue(''); // reset if current choice is no longer legal
}
affectedCell.setDataValidation(optionA);
}
else {
affectedCell.setDataValidation(optionsABC);
}
}
}
Best Answer
See this example spreadsheet. Sheet
Countriescontains source data (instead of prices and products, I've used countries, population and other data, but the concept is the same). SheetDropdowncontains the example. In that latter sheet, cellC1andE1are dropdown menus (use theData→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 theCountriessheet, 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 theCountriessheet, where column C is lower thanDropdown!C1and column E is lower thanDropdown!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
you can enter the list of values you want users to select from by setting the
Data→Validation…menu. For instance, for cellC1of theDropdownsheet,Criteriato 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
Criteriais 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 sheetPopulation rangewhere I made some computations to generate a log scale list of values between the smallest country population and the largest.Hope this helps.