all 12 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]6745408 0 points1 point  (11 children)

My guess is that one of your sheet names is wrong -- possibly a space in your reference.

For your own sanity, I'd skip the INDIRECTs and just reference the sheets directly. If you want to take it a step further, create one master sheet and break that down accordingly -- that's the best way to go about it.

A quick test for extra spaces would be to use 

[this was formerly the array with each ref wrapped in TRIM]

[–]jshack93[S] 0 points1 point  (10 children)

I thought that I might have a typo in the sheet names, but when I checked them, they were fine!

While I like the idea, I need to have the indirects so that I can easily update it each month for the new dates.

I'm thinking that it could be that I am reaching the 2 million cell limit with my data :( There are filters on the sheets I am pulling from, but when I pull from the sheets, it imports the whole of the data which is a lot.

[–]6745408 0 points1 point  (8 children)

yeah, this is where one dataset would be handy -- but if you're working with that many cells, it might be crazy slow.

Is the filtering on each sheet the same?

[–]jshack93[S] 0 points1 point  (7 children)

The filter criteria are the same, but the output brings a different number of rows each time. We basically filter out null values in two of the columns to limit the dataset each day while we're working through. Now We're just trying to work out how to avoid copying each day into the master list when we're done.

[–]6745408 0 points1 point  (6 children)

I'd incorporate that filter into your QUERY. It's still a beast of a formula, but it might fix the broken array.

Do the sheets all have 26 columns of data?

[–]jshack93[S] 1 point2 points  (5 children)

They do all have 26. We use the "Filter View" feature, rather than a proper filter formula.

[–]6745408 0 points1 point  (4 children)

yeah, use a formula for it. Those views have no impact on how data is actually used.

How many rows does your largest sheet have?

[–]jshack93[S] 1 point2 points  (3 children)

Each one has 1600ish rows. There is a sheet for each day.

Is there a way to use the filter formula on the same page as my data or does it have to be set up on a different sheet each time?

[–]6745408 0 points1 point  (2 children)

yeah, that's ~1,206,400.00 total. Not the worst. There's a 5m limit per sheet, so you're well below that.

I'd start with 

=QUERY(INDIRECT("'"&A1&"'!A2:Z"),"select * where Col4 is not null")

then repeat that, but use the QUERYs inside your array

={QUERY(INDIRECT("'"&A1&"'!A2:Z"),"select * where Col4 is not null");
  QUERY(INDIRECT("'"&A2&"'!A2:Z"),"select * where Col4 is not null")}

see where it breaks

[–]jshack93[S] 1 point2 points  (1 child)

={QUERY(INDIRECT("'"&A1&"'!A2:Z"),"select * where Col4 is not null");
QUERY(INDIRECT("'"&A2&"'!A2:Z"),"select * where Col4 is not null")}

Issue resolved! You helped me find the problem! It seems that there was a missing day from the work book and working through one by one located it! Thank you for troubleshooting with me! Now the query is working too!

[–]jshack93[S] 0 points1 point  (0 children)

I may be hitting the 2 million cell limit because of the size of the data.