How to fix Filter formula #SPILL! error by change to use the VBA

[alantse2010]

Hi all, i am trying to use filter formula to filter out the result, it works, but if every row use this formula, it will cause #spill! error as sometimes return more than one result

May i know is there have any method to update the formula for auto insert the row to avoid #spill? error in using filter formula. Is VBA ok?

Thank you very much for your help.

IT platform_V4.7_LT.xlsm
	A	B	C
18	Po#	PO issue Date	Pemrit Reference
19	4501296136	Friday, 28 August 2020	WELT2018-0348-01
20	#SPILL!	#SPILL!	WELT2018-0348-02
21	#SPILL!	#SPILL!	WELT2018-0348-03
Project Management Protocol

Cell Formulas
Range	Formula
A19:A21	A19	=IF(ISBLANK(C19),"",IFERROR(IFERROR(FILTER(FILTER('https://my.shareppoint.com/sites/abc/[Master PO List_LTV0.xlsm]Sheet1'!$B$4:$G$500000,'https://my.shareppoint.com/sites/abc/[Master PO List_LTV0.xlsm]Sheet1'!$G$4:$G$500000=C19,""),{1,0,0,0,0,0}),FILTER(FILTER('https://my.shareppoint.com/sites/abc/[Master PO List_LTV0.xlsm]Sheet1'!$B$4:$G$500000,'https://my.shareppoint.com/sites/abc/[Master PO List_LTV0.xlsm]Sheet1'!$G$4:$G$500000=C19,""),{1,0,0,0,0,0})),"Pending"))
B19:B21	B19	=IF(ISBLANK(C19),"",IFERROR(IFERROR(FILTER(FILTER('https://my.shareppoint.com/sites/abc/[Master PO List_LTV0.xlsm]Sheet1'!$B$4:$G$500000,'https://my.shareppoint.com/sites/abc/[Master PO List_LTV0.xlsm]Sheet1'!$G$4:$G$500000=C19,""),{0,0,0,1,0,0}),FILTER(FILTER('https://my.shareppoint.com/sites/abc/[Master PO List_LTV0.xlsm]Sheet1'!$B$4:$G$500000,'https://my.shareppoint.com/sites/abc/[Master PO List_LTV0.xlsm]Sheet1'!$G$4:$G$500000=C19,""),{0,0,0,1,0,0})),"Pending"))
C19:C21	C19	=IF(ISBLANK(B3),"",FILTER(FILTER('https://my.shareppoint.com/sites/abc/[Permit (LT).xlsm]Permit Detail'!$F$3:$H$500000,'https://my.shareppoint.com/sites/abc/[Permit (LT).xlsm]Permit Detail'!$F$3:$F$500000=B3,""),{0,0,1}))
Dynamic array formulas.

 

[duggie33]

To avoid the SPILL error, your formula should only return a single value. Can you wrap your formula in MAX, MIN, or another function to return a single value? The data you supplied references a Sharepoint spreadsheet so we cannot use your data. Your formula may be too complicated, there is an ISBLANK, two IFERRORS, and four FILTER functions in a single formula.

If you can provide data that can be used by others, I am positive someone can help you find a solution.

Hope that helps,

Doug

 

[alantse2010]

To avoid the SPILL error, your formula should only return a single value. Can you wrap your formula in MAX, MIN, or another function to return a single value? The data you supplied references a Sharepoint spreadsheet so we cannot use your data. Your formula may be too complicated, there is an ISBLANK, two IFERRORS, and four FILTER functions in a single formula.

If you can provide data that can be used by others, I am positive someone can help you find a solution.

Hope that helps,

Doug

Thank you for your reply.
I wrapped my formula to return a single value.