I am tying to build a multi-dynamic GOOGLE sheet query and have included a sample sheet

Question

I have been working on this for HOURS and can't seem to figure out the piece I am missing. Basically I have a sheet that I have built that has 8 tabs of representatives of 8 different companies and, if we are planning an event in, say, Dallas, it produces all of the reps within 100 miles to give us a list of who to reach out to. I have been hardcoding the states and I want to make that part dynamic. So I have the companies (in a certain tab) listed in column N, and I have: K4: QUERY( N4: order by W") U3: = " " & U4 U4: =TEXTJOIN(" OR W CONTAINS ", TRUE, T:T)

In S I have states, in R I have check boxes and in T, for example T8, =if(R8=TRUE,S8,"") Finally I have o1 =CONCATENATE($K$4,M4,$N$4,$U$3,$N$3) That is effectively building this string: QUERY(CompanyB!S:Z, "Select * where W contains CT OR W CONTAINS ME OR W CONTAINS ID OR W CONTAINS CA OR W CONTAINS TX order by W")

I am trying to build it dynamically, as I said. I am hitting walls. I have so far hard coded =QUERY(CompanyA!S:Z, "Select * where W = '"&U3&"'")

And it doesn't fail but it also doesn't return data.

To make this all make a lot more sense feel free to check out https://docs.google.com/spreadsheets/d/1DaYcpnmV4fLm76SFvV63ujGmARaRdn6OhK4yPA_pTs0/edit?usp=sharing

I know this is a complicated sheet, and this is just a sub-sheet where it will then pull distance etc; I am not looking for someone to figure this out for me, but if anyone has a direction to point me in? I am not used to hitting solid walls on Gsheets, and I have used up my max GPT4 paid and Claude Opus paid (I can still access GPT4o or Sonnet, just demonstrating how hard the wall is I am hitting), and I have been Appscripting a lot longer than I have been CHATGPT'ing... Any help is appreciated.

Answer 1

Choose Insert > Sheet and put this formula in cell A1 of the new sheet:

=let( 
  stackSheets_, lambda(sn,ra,h,reduce(h,sn,lambda(rs,s,let(r,indirect(s&"!"&ra),l,max(index(row(r)*iferror(r<>""))),d,filter(to_text(r),row(r)<=l),r_,lambda(v,byrow(d,lambda(_,v))),if(counta(iferror(d)),vstack(rs,hstack(r_(s),d)),rs))))), 

  sheetNames, filter(Sheet13!M2:M, len(Sheet13!M2:M)), 
  rangeA1, "S2:AA", 
  headers, hstack("Sheet name", CompanyA!S1:AA1), 
  statesList, join("|", filter(Sheet13!S3:S, Sheet13!R3:R)), 
  data, stackSheets_(sheetNames, rangeA1, headers), 

  query(data, "where Col6 matches '" & statesList & "' ", 1) 
)

See let(), lambda(), hstack(), join(), filter() and query().