Google Sheets Index query for Multiple Sheets

Question

Good afternoon,

I have a total of 5 Data entry sheets and need assistance on creating a master query that returns a consolidated list (from all Data entry sheets), more specifically a list of names who either received a 'No' or '0' or '1' on the Pre or Post assessment.

Dummy Spreadsheet

Expected Result, in **Index** sheet:

**Standard:** (Data entry columns C4:BK4) 
**Student Name:** (Data entry column A) of those that have a 'No' in either the 'Pre' or 'Post' for each Standard (see: columns C4:AG4 on the 3-5h example).
**Pre:** Returns - No, 0 or 1 (from each Pre column in Data entry) 
**Post:** Returns - No, 0 or 1 (from each Pre column in Data entry) 
**Cross Cutting Concept:** (Data entry columns C4:BK5) 

A previous query iteration returned all 'No' values from C8:AF="No" . On the Index sheet, columns T:BL shows an example of the initial query and result, but I would like to revise as indicated and consolidate into one master query.

=index(split(tocol(if('Kindergarten -  Data Entry'!C8:AF="No",scan(,'Kindergarten -  Data Entry'!C4:AF4,lambda(a,c,if(c="",a,c)))&"|"&'Kindergarten -  Data Entry'!A8:A,),1,1),"|"))


Data Entry Sheets:
Kindergarten -  Data Entry
1st Grade -  Data Entry
2nd Grade -  Data Entry
3rd Grade -  Data Entry
4th Grade -  Data Entry
5th Grade -  Data Entry

Any assistance is greatly appreciated, thanks.

Answer 1

Added one solution here for you to test out:

=reduce(tocol(,1),tocol(A2:A,1),lambda(a,c,vstack(a,let(Σ,index(match(,0/(indirect(c&"!A:A")<>""))),
 rep_,indirect(c&"!A8"):index(indirect(c&"!A:A"),Σ), grp_,hstack(indirect(c&"!C8"):index(indirect(c&"!AF:AF"),Σ),indirect(c&"!AH8"):index(indirect(c&"!BK:BK"),Σ)), header_,hstack(indirect(c&"!C4:AF4"),indirect(c&"!AH4:BK4")), header_2,hstack(indirect(c&"!C5:AF5"),indirect(c&"!AH5:BK5")), size_,2,
 Λ,reduce(tocol(,1),sequence(columns(grp_)/size_,1,1,size_),lambda(a,c,vstack(a,hstack(wrapcols(index(header_,c),rows(rep_),index(header_,c)),rep_,choosecols(grp_,sequence(size_,1,c)),wrapcols(index(header_2,c),rows(rep_),index(header_2,c)))))), 
 filter(Λ,regexmatch(index(Λ,,3)&"","No|1|0")+regexmatch(index(Λ,,4)&"","No|1|0"))))))