Sunday, October 30, 2011

yogi_Use QUERY Function With Search Criteria Defined By Data Validation DropDown

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
user kinngrimm said:
How do i get a value into a query defined by a dropdown(data validation) menu?
Firefox 7.01, Win7(64)
I have 2 dropdown menus(Data validation) which at some point should be used by another cell to get the values out of a table
1. dropdown menue
type: light,medium,heavy
2. dropdown menue
column names: foo,bar,foobar
The table looks like
type         foo          bar         foobar
light        40%          20%          10%
medium       50%          30%          40%
heavy        10%
          70%          40%
The table is completly in a 
Range as Base for the Query
The Query should give me back f.e. '50%'
=QUERY(Range,"foo' where 'type'='medium'")
1. Problem
Atm it is static in a cell but i dont get anything back, the cell i n which idefined thr query
looks like a function call
2. Problem
Even if i would get back the value out of the matrix, i noticed that i cant put in the query dynamic changes so that when i change the dropdown menu, the outcome of the cell with the query would change
Any help and/or advice would be appreciated

following is one solution to the problem