0

I am using the query function to gather data from other sheets in the same workbook. It only pulls over the data from the "teacher" sheets when at least 6 rows of data are entered. The 6 rows of data can be entered on one teacher sheet or a combination of any of the teacher sheets. If only 1 or 2 rows are entered, nothing will be pulled to the summary sheets. I'm very new to this, so I'm sure it is something I am simply not seeing.

So basically, my teachers put in their data each day under their teacher tabs and the 3 summary pages pull from there or each other. Right now I have 5 entries under teacher 1 and 1 entry under teacher 2 but if you clear one of any row, it stops pulling the data to the summaries.

https://docs.google.com/spreadsheets/d/1UBW1JPfjmm-hRaLTDF-_4xCFaQsE5ftHh77K5fmJ9ao/edit?usp=sharing

Improve this question
3
  • Hi, welcome to WebApps. I just deleted the data in the last row (I didn't delete the row), and the data is pulling through. You also don't have any data on Teachers2+ so we can't evaluate the extent of the problem. Would you please supply a little more information so we can better understand your problem. Which sheet has the problem query, what cells contain the query, what is the source of the query. It might be useful for you to include a sample sheet showing how if an entry is deleted, then the data stops pulling through.
    – Tedinoz
    Commented Jul 8, 2022 at 2:13
  • Your spreadsheet has been changed. Teacher1 (which was populated) is now blank; Teacher3 (which was empty) is not populated. Whatever the case, would you please edit your question to align to the data in your spreadsheet.
    – Tedinoz
    Commented Jul 8, 2022 at 5:37
  • I'm sorry. I've still been trying to figure this out. It doesn't really matter which "teacher" sheet is used (1 or many), there must be 6 or more rows of data for it to work. So, if you remove 1 row of data, and then look at any of the 1st 3 summary pages, it stops working.
    – Tracy McAnally
    Commented Jul 8, 2022 at 12:37

1 Answer 1

Reset to default
0

Your existing formula uses range names in the "Data" parameter of the query. Instead use specific sheet/row/column references in the "Data parameter.

All Data Pull - Cell A4 =QUERY({Teacher1!A1:H;Teacher2!A2:H;Teacher3!A2:H;Teacher4!A2:H;Teacher5!A2:H;Teacher6!A2:H;Substitute!A2:H}, "SELECT Col1, Col5, Col3, Col6 where Col6 is not null Order by Col1",1)

Specific Data Pull - Cell A4 =QUERY({Teacher1!A1:H;Teacher2!A2:H;Teacher3!A2:H;Teacher4!A2:H;Teacher5!A2:H;Teacher6!A2:H;Substitute!A2:H}, "SELECT * WHERE Col1 >= DATE '" &TEXT(C2,"yyyy-MM-dd")& "' AND Col1 <= DATE '" &TEXT(E2,"yyyy-MM-dd")& "' and Col6 is not null Order by Col5, Col3, Col1",1)

Summary Page

No change required. Formula on this page appear to reference "All Data Pull".

Improve this answer
6
  • Thank you so much. I'm not sure I understand why the headers were the issue but it seems to work. However, now I'm having to change a few other formulas to accommodate and they are not summing up the minutes. Specific Data Pull formula to change is located in J5 and All Data Pull formulas are at f5, j5, n5, etc. Any ideas here?
    – Tracy McAnally
    Commented Jul 9, 2022 at 1:10
  • Note: I have changed the formula in the answer. The effect is that the result will provide the Column Headings.
    – Tedinoz
    Commented Jul 10, 2022 at 4:56
  • Formula for Specific Data Pull-Cell J4: =query({QUERY({Teacher1!A1:H;Teacher2!A2:H;Teacher3!A2:H;Teacher4!A2:H;Teacher5!A2:H;Teacher6!A2:H;Substitute!A2:H}, "SELECT * WHERE Col1 >= DATE '" &TEXT(C2,"yyyy-MM-dd")& "' AND Col1 <= DATE '" &TEXT(E2,"yyyy-MM-dd")& "' and Col6 is not null Order by Col5, Col3, Col1",1)},"select Col5,Col3, SUM(Col6) where Col5 is not null group by Col5, Col3 label Sum(Col6) 'Minutes Provided'",1).
    – Tedinoz
    Commented Jul 10, 2022 at 4:58
  • Formula for All Data Pull-Cell F4: =query({QUERY({Teacher1!$A1:$H;Teacher2!$A2:$H;Teacher3!$A2:$H;Teacher4!$A2:$H;Teacher5!$A2:$H;Teacher6!$A2:$H;Substitute!$A2:$H}, "SELECT Col1, Col5, Col3, Col6 where Col6 is not null Order by Col1",1)},"select Col2, Col3, Sum(Col4) where Col1 >= DATE '"&TEXT(DATEVALUE(F3),"yyyy-mm-dd")&"' and Col1 <= DATE '"&TEXT(DATEVALUE(G3),"yyyy-mm-dd")&"' group by Col2, Col3 label Sum(Col4) 'Minutes provided'"). Copy this formula from F4 to J4, N4, etc
    – Tedinoz
    Commented Jul 10, 2022 at 5:11
  • This is perfect! I'm sorry it has taken me so long to respond. I really appreciate your help!
    – Tracy McAnally
    Commented Jul 13, 2022 at 21:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.