CODEWITHSUNDEEP
google-sheets
Luckyn
Luckyn

Reputation: 563

google spreadsheet query to dropdown list

I'm working on a google spreadsheet and have to do a dropdown list depending on some criteria. For example I have a DB page with :

|   A  |   B   |
| name | price |
| a    | 100   |
| b    | 120   |
| c    | 150   |

If I want to get a list of all item I know I can do it with the formula, going to data > validation and say the range DB!A3:A999 as explained here : https://support.google.com/docs/answer/186103

Now I would like to add some possible filter for the user like min & max price.

I think I need to use the query formula like =QUERY(DB!3:999, "SELECT A WHERE B>=110 AND B<=140") but this is returning all correct result in all case under where I put the formula (1 row = 1 result). Is there a way to turn this query into a dropdown list ? or any other way to add criterias to the validation range ?

EDIT

Here is a spreadsheet link if you want to test your solution before posting https://docs.google.com/spreadsheets/d/1mwgBla7DyIHio8eH3xr2w_EngvHcHIeTXwc43e7YwwU/edit?usp=sharing

Upvotes: 2

Views: 21075

Answers (1)

Akshin Jalilov
Akshin Jalilov

Reputation: 1716

Yes, you can do something like this:

Say cell B1 is a dropdown list you can write your query:

=QUERY(DB!3:999, "SELECT A WHERE B='"&B1&"' ")

However this will only work with plain text.

I think the better option fro you would be to use FILTER formula.

Upvotes: 4

Related Questions