Sheets - Custom Data Validation Combobox

Question

I am having a problem with making my own custom data validation combobox, here is what I am having a problem with:

Sheet (Hardware) Column A Column S:

Column A = Asset Tag

Column S = is Owned? Yes/No

Sheet (Assigned_Hardware) Column F:

Column F = Asset Tag

Now, what I want is for the Assigned_Hardware 'F' to display all the items listed from Hardware 'A' after confirming the value of Hardware 'S'is No.

I am a total noob with the Google Sheet and Excel. I can only do simple range combobox.

Answer 1

You can't create a data validation dropdown using a custom formula such as query or filter.

However, there is a workaround:

• create the relevant query/filter on another sheet,
• refer to the data range created by the query/filter when you create the Data validation.

---

Example:

• create a sheet called, say, "Validation_data".
  • in cell A1, enter "Assigned_Hardware-Asset Type" to identify the purpose of the validation. (This is purely for identification.)
  • in cell A2, enter this formula
    =query({Hardware!A2:A,Hardware!S2:S},"select Col1 where Col2 = 'No'").
    This creates a range ("A2:A") that will contain all the Asset Tags where the asset is not owned.
• in Sheet "Assigned_Hardware", Cell F2 (or whatever cell you choose to use), create the Data Validation:
  • Select Data, Data Validation
    • Criteria: List from a Range
    • Range = "Validation_data!A2:A" (you can select it with the mouse)
    • Save the Data Validation.

Note: a valid alternative might be to create a named range for the query, such as:

• =query(<<create name range on the Hardware tab>>,"select A, S where S = 'No'",1).

---

Note:

Even though the reference to the data range on "Validation_data" is open-ended, Google Sheets will convert it to include the last row on the "Validation_data" sheet (e.g. "Validation_data!A2:A1000"). Depending on the number of assets, you may wish to reduce the number of rows on "Validation_data" to avoid blowout.