Help with dynamic Filter that brings Ref!

[josvill2010]

HI Everybody, When using the dynamic filter option I get the message SPILL!.
To fix it I added more rows for which I am getting the ref! error but they are in lines that they are not bringing new data.
I would like to delete them but when I do that the spill message comes back.
In theory I should need only one row in this case.

Vendor	Requestor	PO #	Amount DKK	UnBilled PO Currency	POC
Vendor name 1	Joe 1	2110003687​	$182,267	$18,640	Joe 1
#REF!​	#REF!​	#REF!​	#REF!	#REF!
#REF!​	#REF!​	#REF!​	#REF!	#REF!
#REF!​	#REF!​	#REF!​	#REF!	#REF!
#REF!​	#REF!​	#REF!​	#REF!	#REF!

 

[Fluff]

What is the formula you are using & what is you data like?

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.

 

[josvill2010]

HI Everybody, When using the dynamic filter option I get the message SPILL!.
To fix it I added more rows for which I am getting the ref! error but they are in lines that they are not bringing new data.
I would like to delete them but when I do that the spill message comes back.
In theory I should need only one row in this case.

Vendor	Requestor	PO #	Amount DKK	UnBilled PO Currency	POC
Vendor name 1	Joe 1	2110003687​	$182,267	$18,640	Joe 1
#REF!​	#REF!​	#REF!​	#REF!	#REF!
#REF!​	#REF!​	#REF!​	#REF!	#REF!
#REF!​	#REF!​	#REF!​	#REF!	#REF!
#REF!​	#REF!​	#REF!​	#REF!	#REF!

LLA_Vendors_DPC Calendar spend_2022.xlsx
	A	B	C	D	E	F
3	Vendor	Requestor	PO #	Amount DKK	UnBilled PO Currency	POC
4	Vedndor 1	Joe1	2110003687	$182,267	$18,640	Joe1
LLA

Cell Formulas
Range	Formula
A4:E15	A4	=INDEX(FILTER('Open PO'!A:I,IFERROR(('Open PO'!E:E=U16)*('Open PO'!B:B=LLA!$B$1)*('Open PO'!G:G<>""),0),""),SEQUENCE(COUNTIFS('Open PO'!E:E,U16,'Open PO'!G:G,"<>")),{3,6,7,8,9})
F4	F4	=B4
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
'Open PO'!_FilterDatabase	='Open PO'!$A$3:$AR$277	A4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E1:E3,E30:E33,E35:E45,E47:E54,E56:E60,E62:E66,E68:E72,E74:E78,E80:E83,E85:E90,E16:E20,E27,E122,E159:E1048576	Cell Value	>=500000	text	NO

 

[Fluff]

Thanks for that, but I don't see the problem, your formula seems to be spilling quite happily.

 

[josvill2010]

Thanks for that, but I don't see the problem, your formula seems to be spilling quite happily.

it creates unneeded 11 rows under with error Ref!. It's supposed to be just one row.
By the way how can I delete the names from the previous table?

LLA_Vendors_DPC Calendar spend_2022.xlsx
	A	B	C	D	E	F
3	Vendor	Requestor	PO #	Amount DKK	UnBilled PO Currency	POC
4	Vendor 1	Joe 1	2110003687	$182,267	$18,640	0
5	#REF!	#REF!	#REF!	#REF!	#REF!
6	#REF!	#REF!	#REF!	#REF!	#REF!
7	#REF!	#REF!	#REF!	#REF!	#REF!
8	#REF!	#REF!	#REF!	#REF!	#REF!
9	#REF!	#REF!	#REF!	#REF!	#REF!
10	#REF!	#REF!	#REF!	#REF!	#REF!
11	#REF!	#REF!	#REF!	#REF!	#REF!
12	#REF!	#REF!	#REF!	#REF!	#REF!
13	#REF!	#REF!	#REF!	#REF!	#REF!
14	#REF!	#REF!	#REF!	#REF!	#REF!
15	#REF!	#REF!	#REF!	#REF!	#REF!
16
LLA

Cell Formulas
Range	Formula
A4:E15	A4	=INDEX(FILTER('Open PO'!A:I,IFERROR(('Open PO'!E:E=U16)*('Open PO'!B:B=LLA!$B$1)*('Open PO'!G:G<>""),0),""),SEQUENCE(COUNTIFS('Open PO'!E:E,U16,'Open PO'!G:G,"<>")),{3,6,7,8,9})
F4	F4	=B4
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
'Open PO'!_FilterDatabase	='Open PO'!$A$3:$AR$277	A4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E1:E3,E30:E33,E35:E45,E47:E54,E56:E60,E62:E66,E68:E72,E74:E78,E80:E83,E85:E90,E16:E20,E27,E122,E159:E1048576	Cell Value	>=500000	text	NO

 

[Fluff]

Difficult to tell as you haven't posted the actual data, but should the countifs also be looking at col B as the filter function is?

 

[josvill2010]

Difficult to tell as you haven't posted the actual data, but should the countifs also be looking at col B as the filter function is?

thanks for the advice. Let me take a look.

 

[Fluff]

Another option if you have the LET function is

=LET(f,FILTER('Open PO'!A:I,IFERROR(('Open PO'!E:E=U16)*('Open PO'!B:B=$B$1)*('Open PO'!G:G<>""),0),""),INDEX(f,SEQUENCE(ROWS(f)),{3,6,7,8,9}))

 

[josvill2010]

thanks for the advice. Let me take a look.

it did work. Thanks very much.

 

[Fluff]

You're welcome & thanks for the feedback.