FILTER formula that will hide blank rows.

[lite4d]

I have a spreadsheet that I want to filter data based on dates and the data will range from 3 rows to 120 rows based on the dates that are chosen. What I need to do is add something in the formula to shrink the rows to just the ones with data and expand with the data as required. My current formula is below:

=FILTER('Random Receipts'!A2:Q120,('Random Receipts'!K2:K120>=C3)*('Random Receipts'!K2:K120<=D3))

Right now I have to have 120 rows to cover for the data that "could" be there. I want it to shrink and grow as required. What do I need to add to my formula to get that to work?

I have tried a few things but nothing is working correctly. I just get the $spill error.

Thanks for the help as always!!

 

[Fluff]

Are you putting the formula into a Table, or just a normal range?

 

[lite4d]

Just a normal range that is drawing from another page.

 

[Fluff]

In that case if you are getting a #spill error it means there is something in the way of the formula, so you need to clear all the cells the formula is trying to spill into.

 

[lite4d]

I got the #spill error fixed now I need to get rid of the blank rows.

 

[Fluff]

What values do you have in col K & in C3 & D3

 

[lite4d]

What values do you have in col K & in C3 & D3

Col K in the other sheet is the date that I am using to filter the date by. C3 and D3 are the date ranges that I use to filter the data that I want to see.

 

[Fluff]

In that case there should not be any blank rows.
Are you just putting the formula in a single cell & letting it spill?

 

[lite4d]

Yep. it filters perfectly but it does not shrink or grow as the dates are changed. An example, I have 3 rows per day and for the month I could have up t0 93 rows. If I only choose 1 day I only want to see three rows not 3 full and 90 empty. 2 days 6 filled 87 not......... Is that possible based on the current formula? If I try to delete only the first row I get the #spill error because the data has nowhere to go.

 

[Fluff]

I don't understand what you mean by this

If I try to delete only the first row I get the #spill error

The formula should only be in a single cell in that row, so if you delete it, there is no formula to give an error.