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]

Can you post some sample data, from both sheets with the formula in place.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[lite4d]

Yeah I wrote that wrong If I leave just the first row with the formula in it I get the #spill error.

 

[lite4d]

Can you post some sample data, from both sheets with the formula in place.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I'm very limited on my work computer as to what I can download. I moght can send it to my home and do it form there. Let me try that.

 

[lite4d]

I'm very limited on my work computer as to what I can download. I moght can send it to my home and do it form there. Let me try that.

20231218 MRC Template - WM - I-Level Repair (1).xlsx
	A
15
Receipt to Stow

 

[Fluff]

You need to select the range of cells you want to display, not a single cell.

 

[felixstraube]

If for example you have this data:

RandomReceipts.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	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
2	[^S\	IJVT	FYQL	D]OP	I^NE	BGI\	WIHJ	TSHK	QJZ]	O[MI	2024-01-01	EQQS	PTI^	N\BL	PCEC	FRDJ	BM][
3	HAEC	VFED	X^VL	MHWT	JENY	ANPM	EHXH	^GHW	TCN]	CG[P	2024-01-02	[SB]	DZ^B	SV[S	UXBM	JZ[L	XXOG
4	XWYI	XCG[	ZMWK	PQJS	HDC]	FVDJ	USIM	AH]\	IWQI	OC]K	2024-01-02	[AUN	[GAT	S\II	SZQF	AOY[	TKEG
5	ASIR	RBUW	XX[]	BC\I	NQBV	]XZ[	V]RE	U^N^	[LVK	VRMA	2024-01-02	JCGR	]I^Y	FUEV	^^DM	IOCJ	DUQZ
6	UHMU	TYMZ	HOIY	GIMC	PRHA	HLIM	QZMJ	KMZY	FWU]	[KHS	2024-01-02	^JCV	SJ\K	QXPF	PZEU	NPIN	USQG
7	LE]C	ERED	ZJ\U	QUKO	HQFC	MEDU	SEK[	CM[C	YFNP	KDUK	2024-01-03	ZVOG	^S]\	OZBN	WOMG	DRE]	WWWE
8	KNCT	EHWC	PHNB	FL]]	JPA\	KWXC	ANUK	PJON	GXBH	WFR[	2024-01-03	VLWJ	U\BW	N[KZ	KPIR	FPDP	VOBS
9	ZZWN	TCPT	[]LL	AOR[	MTBQ	NYB]	IS[E	DPLO	TYMK	YKVP	2024-01-03	XIW]	FIKN	MBNN	NVSY	XJJ^	V[ON
10	U]PK	]WAW	SNKZ	WAVX	]Y^\	^MAC	MZEJ	JJFL	STEO	TJIE	2024-01-03	GSCU	NZ^]	IXUR	MLAA	POGH	C]X[
11	^BSZ	SZOB	^ZP[	WHAR	RB^\	QJH\	WNKO	LGUP	CKXD	CTEK	2024-01-04	CWGL	U^HX	PQRN	R\XJ	MXGY	TUJX
12	TUCD	LVK\	OMEB	WAIE	PVUC	A\P]	CQSS	TQOG	^VHX	FACA	2024-01-04	MO^S	^QCG	RQXP	LTGY	IVYL	JPM[
13	WERI	UAL]	XV[P	HAWB	HILB	BKIJ	SSGA	NY]C	^EFO	NDI]	2024-01-04	Q^KO	U[DU	AVZS	KH^K	GAFF	HJ[C
14	HBQW	OS[B	KFDV	^LPX	H^HM	ENYJ	MKGG	ZJIS	CNVT	ORUB	2024-01-04	SNY^	GXAD	[BGD	XUDE	FLGB	\LWO
15	K^ZO	WADO	CIOO	QSTA	S[OB	YDAR	[URS	B^EI	PAXH	IOWR	2024-01-05	IOA^	[JZ[	L]\N	RUVA	HX]W	\TTD
Random Receipts

You should get:

RandomReceipts.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3			2024-01-01	2024-01-01
15	[^S\	IJVT	FYQL	D]OP	I^NE	BGI\	WIHJ	TSHK	QJZ]	O[MI	2024-01-01	EQQS	PTI^	N\BL	PCEC	FRDJ	BM][
16
Sheet1

Cell Formulas
Range	Formula
A15:Q15	A15	=FILTER('Random Receipts'!A2:Q120,('Random Receipts'!K2:K120>=C3)*('Random Receipts'!K2:K120<=D3))
Dynamic array formulas.

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

Cell Formulas
Range	Formula
A15:Q19	A15	=FILTER('Random Receipts'!A2:Q120,('Random Receipts'!K2:K120>=C3)*('Random Receipts'!K2:K120<=D3))
Dynamic array formulas.

RandomReceipts.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3			2024-01-01	2024-01-03
15	[^S\	IJVT	FYQL	D]OP	I^NE	BGI\	WIHJ	TSHK	QJZ]	O[MI	2024-01-01	EQQS	PTI^	N\BL	PCEC	FRDJ	BM][
16	HAEC	VFED	X^VL	MHWT	JENY	ANPM	EHXH	^GHW	TCN]	CG[P	2024-01-02	[SB]	DZ^B	SV[S	UXBM	JZ[L	XXOG
17	XWYI	XCG[	ZMWK	PQJS	HDC]	FVDJ	USIM	AH]\	IWQI	OC]K	2024-01-02	[AUN	[GAT	S\II	SZQF	AOY[	TKEG
18	ASIR	RBUW	XX[]	BC\I	NQBV	]XZ[	V]RE	U^N^	[LVK	VRMA	2024-01-02	JCGR	]I^Y	FUEV	^^DM	IOCJ	DUQZ
19	UHMU	TYMZ	HOIY	GIMC	PRHA	HLIM	QZMJ	KMZY	FWU]	[KHS	2024-01-02	^JCV	SJ\K	QXPF	PZEU	NPIN	USQG
20	LE]C	ERED	ZJ\U	QUKO	HQFC	MEDU	SEK[	CM[C	YFNP	KDUK	2024-01-03	ZVOG	^S]\	OZBN	WOMG	DRE]	WWWE
21	KNCT	EHWC	PHNB	FL]]	JPA\	KWXC	ANUK	PJON	GXBH	WFR[	2024-01-03	VLWJ	U\BW	N[KZ	KPIR	FPDP	VOBS
22	ZZWN	TCPT	[]LL	AOR[	MTBQ	NYB]	IS[E	DPLO	TYMK	YKVP	2024-01-03	XIW]	FIKN	MBNN	NVSY	XJJ^	V[ON
23	U]PK	]WAW	SNKZ	WAVX	]Y^\	^MAC	MZEJ	JJFL	STEO	TJIE	2024-01-03	GSCU	NZ^]	IXUR	MLAA	POGH	C]X[
Sheet1

Cell Formulas
Range	Formula
A15:Q23	A15	=FILTER('Random Receipts'!A2:Q120,('Random Receipts'!K2:K120>=C3)*('Random Receipts'!K2:K120<=D3))
Dynamic array formulas.

 

[lite4d]

The data I draw is correct based on the dates I choose. I'm looking to get rid of the blank rows that come after it to avoid the #SPILL error. I have to add 120 rows because after a month I have the potential to have that many rows, but when I only look at 1 day I should only see three rows vice three rows of data and 117 blank rows.

 

[felixstraube]

I don't understand what blank row you mean. Filter will not return any blank rows.
Can you show a screenshot, XL2BB or upload the file?

 

[lite4d]

I don't understand what blank row you mean. Filter will not return any blank rows.
Can you show a screenshot, XL2BB or upload the file?

When I filter by the dates in C3 and D3I get the data I want but I don't want the blank rows below it. As of now, I have to have 120 rows to prevent the #SPILL error. Is there a way to filter only the data and not the blanks? Basically I want the data to grow or shrink and show only the data that meets the date criteria and not the blank rows.

 

[Fluff]

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