Search code examples
if-statementgoogle-sheetsgoogle-sheets-formulagoogle-sheets-query

How do I switch b/w queries using 2 tiered data validation based on lists in Google Sheets

sample sheet

I am trying to create a 2 tiered reporting 2 that relies on data validation and queries (unless you tell me queries won't work here and I should be using some other function).

Here is a visual of the sheet. Included are the queries for each value to return the results i want in A8. I plan to use conditional formatting so that B2 is hidden unless A2=ECR. In a perfect world, when ECR is selected in A2, B2 would default to "ALL" but that's not really a big deal - if it is blank a person can just select all.

sheet

Below are the values in each tier and the Queries they would run.

Tier 1: A2

 - ADP  =QUERY(GlobalAVG!H2:L78,"select H,L",1) (value in tier 2 would be ignored)
 - ECR  (use tier 2)

Tier 2: B2

 - ALL  =QUERY(GlobalAVG!O2:S78,"select O,S",1)
 - QB   =QUERY(GlobalAVG!AA2:AE78,"select AA,AE",1)
 - RB   =QUERY(GlobalAVG!AG2:AK78, "select AG,AK",1)
 - WR   =QUERY(GlobalAVG!AM2:AQ78, "select AM,AQ",1)
 - TE   =QUERY(GlobalAVG!AS2:AW78, "select AS,AW",1)
 - K    =QUERY(GlobalAVG!AY3:BC78, "select AY,BC")

I've tried a few things but can't even get the tier one switches to work. What am I doing wrong?

=iferror(
    IFS(A2="ADP",
             QUERY(GlobalAVG!H2:L78,"select H,L",1),
         A2="ECR",
            QUERY(GlobalAVG!O2:S78,"select O,S",1))
,"NO RESULTS")

This just returns a single cell but seems to "work" because when A2 is blank "No Results" is published.

Solution

  • try:

    =IF(A2="ADP",  QUERY(GlobalAVG!H2:L78,   "select H,L",   1), 
 IF(B2="ALL",  QUERY(GlobalAVG!O2:S78,   "select O,S",   1),
 IF(B2="QB",   QUERY(GlobalAVG!AA2:AE78, "select AA,AE", 1),
 IF(B2="RB",   QUERY(GlobalAVG!AG2:AK78, "select AG,AK", 1),
 IF(B2="WR",   QUERY(GlobalAVG!AM2:AQ78, "select AM,AQ", 1),
 IF(B2="TE",   QUERY(GlobalAVG!AS2:AW78, "select AS,AW", 1),
 IF(B2="K",    QUERY(GlobalAVG!AY3:BC78, "select AY,BC", 1), 
 "NO RESULTS"))))))

    update:

    =IFERROR(
 IF(A2="ADP",  QUERY(GlobalAVG!H2:L78,   "select H,L",   1), 
 IF(B2="ALL",  QUERY(GlobalAVG!O2:S78,   "select O,S",   1),
 IF(B2="QB",   QUERY(GlobalAVG!AA2:AE78, "select AA,AE", 1),
 IF(B2="RB",   QUERY(GlobalAVG!AG2:AK78, "select AG,AK", 1),
 IF(B2="WR",   QUERY(GlobalAVG!AM2:AQ78, "select AM,AQ", 1),
 IF(B2="TE",   QUERY(GlobalAVG!AS2:AW78, "select AS,AW", 1),
 IF(B2="K",    QUERY(GlobalAVG!AY3:BC78, "select AY,BC", 1), 
               QUERY(GlobalAVG!O2:S78,   "select O,S",   1))))))), 
 "NO RESULTS")