How to Spill a formula

[MiniFav]

I have the below formula which i would like to spill and pull in any rows which require a value.

The formula curently works but i have to drag it down to each row which i have had issue before where on the other work sheet if i were to add a row then the formulas adjust to the new row numbers but do not include the new row within the range. Where if i spill then this automatically adjusts including the new row.

The formula below is essentially checking if Cell 1 is empty, check if either Cells 2 and 3 have values, if so return position link required other wise do nothing.

Any help on explaining on how to get this forumla to spill will be appreciated. I have several types of formula i would like to convert to spill, so any explanation along with suggestions will be greatly appreciated too.

AMP Template V5 test.xlsx
	C
1	Task is Hard Time so Position Link expected.
2	POSITION LINK REQUIRED
3
4
5
6
Errors

Cell Formulas
Range	Formula
C2	C2	=IF(AND(Configuration!$B$2:$B$3000="",OR('AB AMP DATA'!$M$2:$M$3000<>"",'AB AMP DATA'!$N$2:$N$3000<>"")),"POSITION LINK REQUIRED","")

 

[MiniFav]

I think ive worked it out myself, its just the order of things needed changing.

AMP Template V5 test.xlsx
	C
1	Task is Hard Time so Position Link expected.
2	POSITION LINK REQUIRED
3
4	POSITION LINK REQUIRED
5
6
Errors

Cell Formulas
Range	Formula
C2:C3000	C2	=IF('AB AMP DATA'!$M$2:$M$3000<>"",IF(Configuration!$B$2:$B$3000="","POSITION LINK REQUIRED",IF('AB AMP DATA'!$N$2:$N$3000<>"",IF(Configuration!$B$2:$B$3000="","POSITION LINK REQUIRED",""),"")),"")
Dynamic array formulas.

 

[RoryA]

FYI it wasn't just the order. AND and OR return single results, not arrays, so they won't spill.

 

[MiniFav]

FYI it wasn't just the order. AND and OR return single results, not arrays, so they won't spill.

Thank you for this information! is there a list or general rule of functions which will prevent the spill?

 

[RoryA]

Generally speaking any function that works with arrays to produce a single result - e.g. SUM, MIN, MAX. That's why they've introduced (though I think it's in Insider builds only at the moment) functions like BYROW.