I am trying to write a query function in Google sheets that draws data from two tabs within the same Google Sheet file. The query function reads as follows:
=QUERY({'Faculty names'!$A$2:$B;'Faculty hours'!$A$2:$H},"select *")
I intend to build on the SELECT function later once I am able to get this step correctly, so that the query can return various values from the 'Faculty hours' tab. For example, for a person X featuring in 'Faculty hours' tab, it will return SUM of hours for X. On the other hand, for person Y, who is not in this tab, but is listed in 'Faculty names', it will return 0. The order of this list will be the same as in 'Faculty names'.
The 'Faculty names' tab has two columns, one header row and then 165 rows of data. The 'Faculty hours' tab has Columns A:H and 14 rows including a header row. Rows A:C in this sheet has data that is entered with validation from the sheet 'Faculty names'. For example, the data in cell A3 (which is the name of a person) would occur somewhere in the "names" column (A) in the tab 'Faculty names'. Obviously, the number of rows and columns in the tab 'Faculty hours' doesn't tally with the tab 'Faculty names'.
When I run the above query, that is supposed to return all the names from the tab 'Faculty names', I get the error message:
In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.
I was trying to understand this error message and how to overcome it. The nearest that I came to is in this link. If I include more number of columns from 'Faculty names' tab (i.e. specify $A2:&H), as suggested in the link mentioned, then the error message doesn't appear, but the query doesn't return anything beyond Col9. The error message if I set Col9 or anything beyond is:
Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col9
Any suggestions will be helpful.
