0

I am trying to pull data based off of selections made in a dropdown list, and would like to combine two (2) statements/formulas into one. Separately, they both work well and achieve what I want them to, but I would like to combine them so that the data being returned shows up in one place.

=ARRAYFORMULA(IF(Options!C9="Cyclon",IMPORTRANGE("1nvZwulmKkuhBNNgltZzR7VYqjrx0LMWuT7v9Kpjnn10","'Standard, Cylon Backbox'!A:F"),IMPORTRANGE("1nvZwulmKkuhBNNgltZzR7VYqjrx0LMWuT7v9Kpjnn10","'Floating Backbox'!A:F")))

=ARRAYFORMULA(IF(Options!C3="AE",IMPORTRANGE("1nvZwulmKkuhBNNgltZzR7VYqjrx0LMWuT7v9Kpjnn10","'Arcade Cabinet'!A:F"),IMPORTRANGE("1nvZwulmKkuhBNNgltZzR7VYqjrx0LMWuT7v9Kpjnn10","'Xtra Cabinet'!A:F")))

Both of these formulas/statements work great on their own, but I am unable to combine them into one statement so that all of the data being called for by BOTH statements can be pulled into one sheet together.

1
0

You can combine the two importrange() calls with an { array expression }, but since you are using open-ended range references, there will be many blank rows in between the two data tables. Assuming that column A contains a value of the same type on all non-blank rows in all data ranges, you can use query() to weed out blank rows like this:

=query( 
  { 
    importrange("1nvZwulmKkuhBNNgltZzR7VYqjrx0LMWuT7v9Kpjnn10", 
      if( 
        Options!C9 = "Cyclon", 
        "'Standard, Cylon Backbox'!A:F", 
        "'Floating Backbox'!A:F" 
      ) 
    ); 
    importrange("1nvZwulmKkuhBNNgltZzR7VYqjrx0LMWuT7v9Kpjnn10", 
      if( 
        Options!C3 = "AE", 
        "'Arcade Cabinet'!A:F", 
        "'Xtra Cabinet'!A:F" 
      ) 
    ) 
  }, 
  "where Col1 is not null", 
  0 
)

Alternatively, replace the open-ended range references A:F with something like A1:F42, and drop the query() wrapper in the formula.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.