Variable named range cell references in Queries

Question

1. The primary sheet has one column with dates; and thousands of rows on this sheet.

2. There are hundreds of other sheets, named by date. The format of each of these other date sheets is identical.

3. I need a query that can use each date on the primary sheet as reference to query a corresponding date sheet.

4. I would like to drag this formula down across all rows, and have the formula ref the date in the row rather than manually enter each cell.

5. Since I can't name the date sheets by date alone, I will need a naming formula that adds a word to the dates.

6. I've tried applying Named-Ranges to the date sheets and referencing them in the cells with import-range, but this did not work. I also tried inserting a link to the named ranges in each date cell, but this did not work either.

7. I've attached a screen grab of the idea. The data is just for illustration, it is fake. Sorry if this is a dumb question, I'm new to this, experimented, researched, could not find a solution. Thanks!

Answer 1

try:

=QUERY({INDIRECT("C"&ROW(C8)&"!A:H")}, 
 "select Col5,Col8 
  where Col3 > date '"&TEXT(C8, "yyyy-mm-dd)&"' 
    and Col4 = 'SALE'
    and Col5 <= "&D8 + (D8*E8)&"
    and Col8 < 1
    and Col8 > 0.01
  order by Col8
  limit 1", 0)