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!!

 

[felixstraube]

I still do not understand what blank rows below it you mean? The rows from 21 onwards?
Do you get a spill because you have data in rows 135? and you formula returns more than 120 rows?

 

[lite4d]

I still do not understand what blank rows below it you mean? The rows from 21 onwards?
Do you get a spill because you have data in rows 135? and you formula returns more than 120 rows?

When I select dates in C3 and D3, it filters the data that I see in B15:R15 and gives me the ones that meet that criteria below it. If its 3,6,9 rows. The way it shows now is that I have 3 rows of data and 117 empty rows below it that I would like to not see. If I have 2 days then I would see 6 rows of data and 114 empty rows below it. I would only like to see the rows that have data and not the rows below it. If my data pulls 35 rows then I want to just see those 35 rows. Is there a way to hide those empty ones below?

 

[Fluff]

Is there a way to hide those empty ones below?

As we have both said, that formula (if entered correctly) will not return blank rows.
Can you please answer my question in post#20

 

[felixstraube]

Is there something at the bottom that you WANT to see? "Stacked" (so to speak) below your result from the filter function?

Like a footer to your table?

 

[lite4d]

If the formula is in B15 only then you should not get any blank rows.
What error message do the error flags show?

The only errors I get are on the spill cells and that is "unprotected formula"

 

[Fluff]

In that case I need to see your data & how your applying the formula, as there is no way that formula should return blank cells.

 

[felixstraube]

Do you want something like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3			2024-01-01	2024-01-01
11
12	Header 1	Header 2	Header 3	Header 4	Header 5	Header 6	Header 7	Header 8	Header 9	Header 10	Header 11	Header 12	Header 13	Header 14	Header 15	Header 16	Header 17
13	[^S\	IJVT	FYQL	D]OP	I^NE	BGI\	WIHJ	TSHK	QJZ]	O[MI	45292	EQQS	PTI^	N\BL	PCEC	FRDJ	BM][
14	Footer 1	Footer 2	Footer 3	Footer 4	Footer 5	Footer 6	Footer 7	Footer 8	Footer 9	Footer 10	Footer 11	Footer 12	Footer 13	Footer 14	Footer 15	Footer 16	Footer 17
Sheet1

Cell Formulas
Range	Formula
A13:Q14	A13	=VSTACK(FILTER('Random Receipts'!A2:Q120,('Random Receipts'!K2:K120>=C3)*('Random Receipts'!K2:K120<=D3)),{"Footer 1","Footer 2","Footer 3","Footer 4","Footer 5","Footer 6","Footer 7","Footer 8","Footer 9","Footer 10","Footer 11","Footer 12","Footer 13","Footer 14","Footer 15","Footer 16","Footer 17"})
Dynamic array formulas.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3			2024-01-01	2024-01-02
11
12	Header 1	Header 2	Header 3	Header 4	Header 5	Header 6	Header 7	Header 8	Header 9	Header 10	Header 11	Header 12	Header 13	Header 14	Header 15	Header 16	Header 17
13	[^S\	IJVT	FYQL	D]OP	I^NE	BGI\	WIHJ	TSHK	QJZ]	O[MI	45292	EQQS	PTI^	N\BL	PCEC	FRDJ	BM][
14	HAEC	VFED	X^VL	MHWT	JENY	ANPM	EHXH	^GHW	TCN]	CG[P	45293	[SB]	DZ^B	SV[S	UXBM	JZ[L	XXOG
15	XWYI	XCG[	ZMWK	PQJS	HDC]	FVDJ	USIM	AH]\	IWQI	OC]K	45293	[AUN	[GAT	S\II	SZQF	AOY[	TKEG
16	ASIR	RBUW	XX[]	BC\I	NQBV	]XZ[	V]RE	U^N^	[LVK	VRMA	45293	JCGR	]I^Y	FUEV	^^DM	IOCJ	DUQZ
17	UHMU	TYMZ	HOIY	GIMC	PRHA	HLIM	QZMJ	KMZY	FWU]	[KHS	45293	^JCV	SJ\K	QXPF	PZEU	NPIN	USQG
18	Footer 1	Footer 2	Footer 3	Footer 4	Footer 5	Footer 6	Footer 7	Footer 8	Footer 9	Footer 10	Footer 11	Footer 12	Footer 13	Footer 14	Footer 15	Footer 16	Footer 17
Sheet1

Cell Formulas
Range	Formula
A13:Q18	A13	=VSTACK(FILTER('Random Receipts'!A2:Q120,('Random Receipts'!K2:K120>=C3)*('Random Receipts'!K2:K120<=D3)),{"Footer 1","Footer 2","Footer 3","Footer 4","Footer 5","Footer 6","Footer 7","Footer 8","Footer 9","Footer 10","Footer 11","Footer 12","Footer 13","Footer 14","Footer 15","Footer 16","Footer 17"})
Dynamic array formulas.

So that the footer continues after the data pulled from "Random Receipts"?

 

[lite4d]

Is there something at the bottom that you WANT to see? "Stacked" (so to speak) below your result from the filter function?

Like a footer to your table?

Here is the bottom of the page. This shows the bottom of the empty rows that I don't want to see. Down to row 134. So I only want to see the data rows and have it shrink from row 135 to the bottom of the rows of data that I have whether its 3 10 or 20 rows. I'm not sure if that makes sense or not. It might not even be possible.

 

[felixstraube]

Why didn't you say that.
And the answer is no. This is not possible with a formula (that I know of).
You will have to hide the rows. Only way I can think of is VBA.

 

[Fluff]

Are you saying that the spill range shows exactly what you want, rather than what you want plus empty rows inside the spill?