I am trying to organize a long form into a logical sequence so various reasons.
I am doing this on two sheets, one is my master and previous form submissions, and one is my current information that I am setting up for our class registration.
Here's what we are looking at:
ON my master, this formula works perfectly.
=ARRAYFORMULA({
{"Term", "Time Stamp","Family ID", "Family Email", "Participant ID", "Last Name", "First Name", "Participant Email", "Classes"};
SORT(
QUERY({
IF(COUNTA(FILTER('MASTER REGISTRATION'!$E$2:$E, NOT(ISBLANK('MASTER REGISTRATION'!$E$2:$E)))), QUERY('MASTER REGISTRATION'!$A$2:$M, "SELECT A, B, C, D, E, F, G, H, M WHERE E IS NOT NULL", 0), {"","","","","","","","",""});
IF(COUNTA(FILTER('MASTER REGISTRATION'!$O$2:$O, NOT(ISBLANK('MASTER REGISTRATION'!$O$2:$O)))), QUERY('MASTER REGISTRATION'!$A$2:$W, "SELECT A, B, C, D, O, P, Q, R, W WHERE O IS NOT NULL", 0), {"","","","","","","","",""});
IF(COUNTA(FILTER('MASTER REGISTRATION'!$X$2:$X, NOT(ISBLANK('MASTER REGISTRATION'!$X$2:$X)))), QUERY('MASTER REGISTRATION'!$A$2:$AF, "SELECT A, B, C, D, X, Y, Z, AA, AF WHERE X IS NOT NULL", 0), {"","","","","","","","",""});
IF(COUNTA(FILTER('MASTER REGISTRATION'!$AG$2:$AG, NOT(ISBLANK('MASTER REGISTRATION'!$AG$2:$AG)))), QUERY('MASTER REGISTRATION'!$A$2:$AO, "SELECT A, B, C, D, AG, AH, AI, AJ, AO WHERE AG IS NOT NULL", 0), {"","","","","","","","",""})
},
"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9", 0),
6, TRUE
)
})
When I copy the formula to the current class registration and change the tab name in the formula, I get an array formula mistake. I have been looking at this FOR HOURS! Please tell me you see the mistake.
=ARRAYFORMULA({ {"Term", "Time Stamp","Family ID", "Family Email", "Participant ID", "Last Name", "First Name", "Participant Email", "Classes"}; SORT( QUERY({ IF(COUNTA(FILTER('Class Registration'!$E$2:$E, NOT(ISBLANK('Class Registration'!$E$2:$E)))), QUERY('Class Registration'!$A$2:$M, "SELECT A, B, C, D, E, F, G, H, M WHERE E IS NOT NULL", 0), {"","","","","","","","",""}); IF(COUNTA(FILTER('Class Registration'!$O$2:$O, NOT(ISBLANK('Class Registration'!$O$2:$O)))), QUERY('Class Registration'!$A$2:$W, "SELECT A, B, C, D, O, P, Q, R, W WHERE O IS NOT NULL", 0), {"","","","","","","","",""}); IF(COUNTA(FILTER('Class Registration'!$X$2:$X, NOT(ISBLANK('Class Registration'!$X$2:$X)))), QUERY('Class Registration'!$A$2:$AF, "SELECT A, B, C, D, X, Y, Z, AA, AF WHERE X IS NOT NULL", 0), {"","","","","","","","",""}); IF(COUNTA(FILTER('Class Registration'!$AG$2:$AG, NOT(ISBLANK('Class Registration'!$AG$2:$AG)))), QUERY('Class Registration'!$A$2:$AO, "SELECT A, B, C, D, AG, AH, AI, AJ, AO WHERE AG IS NOT NULL", 0), {"","","","","","","","",""}) }, "SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9", 0), 6, TRUE ) })
I put some fake information and reset up the sheets on this doc so you may be able to play with it. The Class Registration Sheet should show the same thing as the master registration and the datastudio list should be the same as the Student Information Sheet:
I am getting an Array literal error. I have counted the columns in each query, there are 9. I have checked the blank, "" there are 9. I have matched parenthesis & matched ellipses (not sure if that's what they are called).
I am newish, I have set these formulas up but the last time I used about 30 helper columns (which is not cool). In between I figured out how to stack queries and stack import ranges (which isn't in here) and then I was so excited when this worked on my master sheet that I am just so annoyed it's not working on the other sheet.
when filter() has no matches, it throws a No matches are found in FILTER evaluation style error & counta() wrapped over the filter function will result in a value of 1 and not 0. for it to be 0 you ought to wrap all your filter functions within ifna() OR iferror()
CHANGE
FILTER('Class Registration'!$O$2:$O, NOT(ISBLANK('Class Registration'!$O$2:$O)))
TO
IFNA(FILTER('Class Registration'!$O$2:$O, NOT(ISBLANK('Class Registration'!$O$2:$O))))