0

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.

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.

5
  • I marked this correct. Thanks! However, I just edited your post for reference. I edited the =query part. – Joshua Dela Cruz Oct 14 '20 at 10:49
  • Thanks, I note you said the "query part wouldn't work". Is that literally true; or perhaps it is a consequence of different locales where one user must use a "comma", and another, a "semi-colon". – Tedinoz Oct 14 '20 at 21:01
  • I think it's not, it's just the select part of the query, it needs to provide the given columns before the where clause. I did it like this, "select A, S where S = 'No',1". And I just created a new named range for my Hardware table where it includes my S column and replace your Hardware!A2:A,Hardware!S2:S – Joshua Dela Cruz Oct 16 '20 at 4:26
  • _ it needs to provide the given columns before the where clause_ FWIW, you could have done the same thing with select Col1, Col2 where Col2 = 'No'. At the risk of telling you what you may already know, the curly brackets in the query have the effect of combining Column A and Column S as two separate ranges, each accessible to the query. Anyway, your edit is fine. PS: Sorry, I managed to lose your actual edit, I hope what I substituted meets your approval. – Tedinoz Oct 17 '20 at 0:07
  • No problem. I was able to get my answer because of your answer. You gave me idea on what and how to do it. – Joshua Dela Cruz Oct 18 '20 at 1:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.