How to write a conditional query formula?

user_11724315 0
Viewed 116

I have a query in google sheets:

=QUERY('Depot Report'!D:Q, "SELECT I, J WHERE P ='"&AK4&"'")'

AK4 is a data validated list of numbers 1-40 which selects all I, J based on the value of AK4.

What I want is "all" in the list that will run the query:

=QUERY('Depot Report'!D:Q, "SELECT I, J")
google-sheets-formula google-sheets
1 Answers

Edit after your comments

The formula always work very well, but your problem in your sheet is:
Values in column P are not numbers because are formatted as text.
No problem. Solution is very easy.

SOLUTION

  1. Select all column P and format from menu Automatic.

  2. Also I changed formula a little for cell S12 for exactly what you need.
    Only copy/paste formula now.

.

=QUERY('Depot Report'!D2:Q, "SELECT I, J WHERE 1=1"&IF(T4="All",""," AND P= "&T4&" "))

Use this formula

=QUERY('Depot Report'!D2:Q, "SELECT I, J WHERE 1=1"&IF(AK4="",""," AND P= "&AK4&" "))
user_154256230
Related