CODEWITHSUNDEEP
google-sheetsselectdate-comparison
Dan Gill
Dan Gill

Reputation: 1

query table based on YEAR(col1) = dropdown cell value

I'm trying to use a drop down menu to pick a year value to summarize my date based on that year.

YEAR    2021    <== Drop Down Menu  
            
DATE    EMPLOYEE    JOB #   CITY
1/2/2020    JIM 25011   Statesville
3/4/2022    BOB 36054   Asheville
6/3/2021    LARRY   32009   Asheville
4/5/2024    LARRY   42027   Johnson City
8/4/2024    JIM 26026   Franklin
9/1/2023    JIM 28032   Greenville
10/3/2024   BOB 25013   Greenville
5/24/2021   LARRY   25019   Franklin

enter image description here DATE is in Date format.

I tried: (in cell E4)

=QUERY(A4:D11,"SELECT A WHERE YEAR(A) = B1")

and I get an error: Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: B1

I tried:

=QUERY(A4:D11,"SELECT A WHERE YEAR(A) = 2024")

and it works.

What am I missing?

Upvotes: 0

Views: 51

Answers (2)

Alma_Matters
Alma_Matters

Reputation: 911

Filter Data by Selected Year

You can also try using the FILTER formula as an alternative solution:

=FILTER(A4:A11,ARRAYFORMULA(year(A4:A11))=B1)

Sample Output: Output

Reference(s):

ARRAYFORMULA

FILTER function

Upvotes: 0

rockinfreakshow
rockinfreakshow

Reputation: 30289

With a slight modification to your current formula:

=QUERY(A4:D11,"SELECT A WHERE YEAR(A) = "&B1, 0)

Upvotes: 1

Related Questions