Spill functions with other functions

[MiniFav]

I have recently been looking into using spill functions more as I can have them veiw an array on another sheet and does not need to be updated if someone inserts a row in the middle of the data. Currently all my formulas will update to make room for the new row but not adjust to include the row.
For example if i have formulas in rows 1,2 and 3 then decide to add a new row between 2 and 3, the formulas will now adjust to cover rows 1,2 and 4, and row 3 will not have a formula.

Below is a table of what i current have, but looking to use a spill function such as SORT or Filter but i am having difficulties getting it to work as intended.
I will be aiming to apply this method to multiple other formulas i have.

For a bit of information i have an error check which searches for several potential errors such as duplicates, numerical values, if one cell is populated then another will need to be populated too, if one cell is populated then other cells shouldn't be, etc

So any explanation on any solutions provided would be appreciated.

Information		Spill function to show duplicate		Formula
1A				=IF(COUNTIF($A$2:$A$10,$A2)>1,"Cust Ref "&$A2&" is Duplicated","")
2A				=IF(COUNTIF($A$2:$A$10,$A3)>1,"Cust Ref "&$A3&" is Duplicated","")
1B				=IF(COUNTIF($A$2:$A$10,$A4)>1,"Cust Ref "&$A4&" is Duplicated","")
2B				=IF(COUNTIF($A$2:$A$10,$A5)>1,"Cust Ref "&$A5&" is Duplicated","")
3A				=IF(COUNTIF($A$2:$A$10,$A6)>1,"Cust Ref "&$A6&" is Duplicated","")
3B		Cust Ref 3B is Duplicated		=IF(COUNTIF($A$2:$A$10,$A7)>1,"Cust Ref "&$A7&" is Duplicated","")
3B		Cust Ref 3B is Duplicated		=IF(COUNTIF($A$2:$A$10,$A8)>1,"Cust Ref "&$A8&" is Duplicated","")
4A				=IF(COUNTIF($A$2:$A$10,$A9)>1,"Cust Ref "&$A9&" is Duplicated","")
4B				=IF(COUNTIF($A$2:$A$10,$A10)>1,"Cust Ref "&$A10&" is Duplicated","")

I am open to other options providing they are formula based and not VBA.

 

[jasonb75]

When you create your mini sheet using XL2BB, try ticking the 'Cell formulas' box and clicking the 'Mini Sheet' button instead of 'Table Only'. Doing that creates a much more useful example for us as it will show which rows and columns are in the sheet and correctly identify the dynamic formulas so that we don't have to try and figure it out from the description.

Although the latter appears to be the more likely choice, as it stands, it is not clear if the middle column contains a formula that spills, or it it is where you want a formula that spills. The correct method to use in such cases would depend on whether the required formula is going to look at an existing spill range or a regular range.

It is also worth noting that not all formulas can spill, while some can be forced into spilling with a little persuasion, the results are often inaccurate.

 

[RoryA]

It looks like you want something like:

=IF(COUNTIF($A$2:$A$10,$A2:$A10)>1,"Cust Ref "&$A2:$A10&" is Duplicated","")

 

[MiniFav]

It looks like you want something like:

=IF(COUNTIF($A$2:$A$10,$A2:$A10)>1,"Cust Ref "&$A2:$A10&" is Duplicated","")

Thank you, thats works! so I just had to use the array as the criteria.

 

[MiniFav]

These are the other fields i am looking at, would you be able to (at a quick glance) tell me if there are going to be any formulas i will not be able to spill here?

AMP Template V4.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Task is Hard Time so Position Link expected.	Check OOP Interval is (‘As Req’, ‘OOP’, ‘Vendor’)	At least one of OOP Interval or Letter Check(R) expected	Days(T) Not Numeric	Days(R) Not Numeric	Months(T) Not Numeric	Months(R) Not Numeric	Years(T) Not Numeric	Years(R) Not Numeric	Hours(T) Not Numeric	Hours(R) Not Numeric	Cycles(T) Not Numeric	Cycles(R) Not Numeric	Can only have Days(T), Months(T) or Years(T)	Can only have Days(R), Months(R) or Years(R)	No Repeat entered and not As Req	(Tab 4): PARENT POSITION not in column POSITION NAMES.	(Tab 4): Duplicate POSITION NAME.	POSITION ATA is missing	POSITION NAME cannot be its own PARENT POSITION.	Check missing Cust Task ref, AMP ref, Description and ATA
2			OOP or LC Required
3			OOP or LC Required
4			OOP or LC Required
Errors

Cell Formulas
Range	Formula
C2:C3	C2	=IF(AMP!D2="************** TASK DELETED **************","", IF(AND(Configuration!B2="",OR('AB AMP DATA'!M5<>"",'AB AMP DATA'!N5<>"")),"POSITION LINK REQUIRED",""))
D2	D2	=IF(AMP!D2="************** TASK DELETED **************","", IF(OR('AB AMP DATA'!P2="As Req",'AB AMP DATA'!P2="OOP",'AB AMP DATA'!P2="Vendor",'AB AMP DATA'!P2=""),"","Tab "& SHEET('AB AMP DATA'!P2) & " Row "&ROW('AB AMP DATA'!P2) &" As Req, OOP or Vendor must be used"))
E2	E2	=IF(AMP!D2="************** TASK DELETED **************","", IF(AND('AB AMP DATA'!B2=""),"",IF(AND('AB AMP DATA'!P2<>"",'AB AMP DATA'!W2=""),"", IF(AND('AB AMP DATA'!P2="",'AB AMP DATA'!W2<>""),"", IF(AND('AB AMP DATA'!P2="",'AB AMP DATA'!W2=""),"OOP or LC Required",IF(AND('AB AMP DATA'!P2<>"",'AB AMP DATA'!W2<>""),"BOTH OOP AND LC HAVE BEEN USED",""))))))
F2	F2	=IF(AMP!D2="************** TASK DELETED **************","", IF('AB AMP DATA'!R2="","",IF(ISNUMBER('AB AMP DATA'!R2),"","TAB "& SHEET('AB AMP DATA'!R2)&" ROW "&ROW('AB AMP DATA'!R2)& " IS NOT NUMERIC")))
G2	G2	=IF(AMP!D2="************** TASK DELETED **************","", IF('AB AMP DATA'!X2="","",IF(ISNUMBER('AB AMP DATA'!X2),"","TAB "& SHEET('AB AMP DATA'!X2)&" ROW "&ROW('AB AMP DATA'!X2)& " IS NOT NUMERIC")))
H2	H2	=IF(AMP!D2="************** TASK DELETED **************","", IF('AB AMP DATA'!S2="","",IF(ISNUMBER('AB AMP DATA'!S2),"","TAB "& SHEET('AB AMP DATA'!S2)&" ROW "&ROW('AB AMP DATA'!S2)& " IS NOT NUMERIC")))
I2	I2	=IF(AMP!D2="************** TASK DELETED **************","", IF('AB AMP DATA'!Y2="","",IF(ISNUMBER('AB AMP DATA'!Y2),"","TAB "& SHEET('AB AMP DATA'!Y2)&" ROW "&ROW('AB AMP DATA'!Y2)& " IS NOT NUMERIC")))
J2	J2	=IF(AMP!D2="************** TASK DELETED **************","", IF('AB AMP DATA'!T2="","",IF(ISNUMBER('AB AMP DATA'!T2),"","TAB "& SHEET('AB AMP DATA'!T2)&" ROW "&ROW('AB AMP DATA'!T2)& " IS NOT NUMERIC")))
K2	K2	=IF(AMP!D2="************** TASK DELETED **************","", IF('AB AMP DATA'!Z2="","",IF(ISNUMBER('AB AMP DATA'!Z2),"","TAB "& SHEET('AB AMP DATA'!Z2)&" ROW "&ROW('AB AMP DATA'!Z2)& " IS NOT NUMERIC")))
L2	L2	=IF(AMP!D2="************** TASK DELETED **************","", IF('AB AMP DATA'!U2="","",IF(ISNUMBER('AB AMP DATA'!U2),"","TAB "& SHEET('AB AMP DATA'!U2)&" ROW "&ROW('AB AMP DATA'!U2)& " IS NOT NUMERIC")))
M2	M2	=IF(AMP!D2="************** TASK DELETED **************","", IF('AB AMP DATA'!AA2="","",IF(ISNUMBER('AB AMP DATA'!AA2),"","TAB "& SHEET('AB AMP DATA'!AA2)&" ROW "&ROW('AB AMP DATA'!AA2)& " IS NOT NUMERIC")))
N2	N2	=IF(AMP!D2="************** TASK DELETED **************","", IF('AB AMP DATA'!V2="","",IF(ISNUMBER('AB AMP DATA'!V2),"","TAB "& SHEET('AB AMP DATA'!V2)&" ROW "&ROW('AB AMP DATA'!V2)& " IS NOT NUMERIC")))
O2	O2	=IF(AMP!D2="************** TASK DELETED **************","", IF('AB AMP DATA'!AB2="","",IF(ISNUMBER('AB AMP DATA'!AB2),"","TAB "& SHEET('AB AMP DATA'!AB2)&" ROW "&ROW('AB AMP DATA'!AB2)& " IS NOT NUMERIC")))
P2:P4	P2	=IF(AMP!D2="************** TASK DELETED **************","", IF(AND(Intervals!A3=""),"",IF(AND(Intervals!F3<>"",Intervals!G3="",Intervals!H3=""),"", IF(AND(Intervals!F3="",Intervals!G3<>"",Intervals!H3=""),"", IF(AND(Intervals!F3="",Intervals!G3="",Intervals!H3<>""),"",IF(AND(Intervals!F3<>"",Intervals!G3<>"",Intervals!H3=""),"TOO MANY CALENDAR INTERVALS",IF(AND(Intervals!F3<>"",Intervals!G3="",Intervals!H3<>""),"TOO MANY CALENDAR INTERVALS",IF(AND(Intervals!F3="",Intervals!G3<>"",Intervals!H3<>""),"TOO MANY CALENDAR INTERVALS",IF(AND(Intervals!F3<>"",Intervals!G3<>"",Intervals!H3<>""),"TOO MANY CALENDAR INTERVALS","")))))))))
Q2	Q2	=IF(AMP!D2="************** TASK DELETED **************","", IF(AND('AB AMP DATA'!B2=""),"",IF(AND('AB AMP DATA'!X2<>"",'AB AMP DATA'!Y2="",'AB AMP DATA'!Z2=""),"", IF(AND('AB AMP DATA'!X2="",'AB AMP DATA'!Y2<>"",'AB AMP DATA'!Z2=""),"", IF(AND('AB AMP DATA'!X2="",'AB AMP DATA'!Y2="",'AB AMP DATA'!Z2<>""),"",IF(AND('AB AMP DATA'!X2<>"",'AB AMP DATA'!Y2<>"",'AB AMP DATA'!Z2=""),"TOO MANY CALENDAR INTERVALS",IF(AND('AB AMP DATA'!X2<>"",'AB AMP DATA'!Y2="",'AB AMP DATA'!Z2<>""),"TOO MANY CALENDAR INTERVALS",IF(AND('AB AMP DATA'!X2="",'AB AMP DATA'!Y2<>"",'AB AMP DATA'!Z2<>""),"TOO MANY CALENDAR INTERVALS",IF(AND('AB AMP DATA'!X2<>"",'AB AMP DATA'!Y2<>"",'AB AMP DATA'!Z2<>""),"TOO MANY CALENDAR INTERVALS","")))))))))
R2	R2	=IF(AMP!D2="************** TASK DELETED **************","",IF('AB AMP DATA'!B2="","",IF('AB AMP DATA'!W2<>"","",IF('AB AMP DATA'!P2="AS REQ","",IF('AB AMP DATA'!P2="","",IF(AND('AB AMP DATA'!P2="OOP",OR('AB AMP DATA'!X2<>"",'AB AMP DATA'!Y2<>"",'AB AMP DATA'!Z2<>"",'AB AMP DATA'!AA2<>"",'AB AMP DATA'!AB2<>"")),"","REPEAT REQUIRED"))))))
S2:S4	S2	=IF(AMP!D2="************** TASK DELETED **************","", IF(Configuration!D2="","",IF(ISNA(VLOOKUP(Configuration!D2,Configuration!B$2:B$3000,1,FALSE)),"No Match on "&"Tab "&SHEET(Configuration!D2)& " Row "&ROW(Configuration!D2),"")))
T2:T4	T2	=IF(AMP!D2="************** TASK DELETED **************","", IF(COUNTIF(Configuration!B:B, Configuration!B2)>1, "Duplicated Position: " & Configuration!B2, ""))
U2	U2	=IF(AMP!D2="************** TASK DELETED **************","", IF(AND('AB AMP DATA'!B2=""),"",IF(AND(Configuration!B2<>"",Configuration!E2=""),"ATA missing","")))
V2:V4	V2	=IF(AMP!D2="************** TASK DELETED **************","", IF(Configuration!D2="","",IF(Configuration!D2=Configuration!B2,"POSITION CANNOT BE ITS OWN PARENT","")))
W2:W4	W2	=IF(AMP!D2="************** TASK DELETED **************","", IF(AND(AMP!B2<>"",AMP!C2<>"",AMP!D2<>"",AMP!F2<>""),"", IF(AND(AMP!B2="",AMP!C2<>"",AMP!D2<>"",AMP!F2<>""),"AMP REF MISSING", IF(AND(AMP!B2<>"",AMP!C2="",AMP!D2<>"",AMP!F2<>""),"CUST TASK REF MISSING", IF(AND(AMP!B2<>"",AMP!C2<>"",AMP!D2="",AMP!F2<>""),"DESCRIPTION MISSING", IF(AND(AMP!B2<>"",AMP!C2<>"",AMP!D2<>"",AMP!F2=""),"ATA MISSING", IF(AND(AMP!B2="",AMP!C2="",AMP!D2<>"",AMP!F2<>""),"AMP + CUST TASK REF MISSING", IF(AND(AMP!B2="",AMP!C2="",AMP!D2="",AMP!F2<>""),"AMP, DESCRIPTION + CUST TASK REF MISSING", IF(AND(AMP!B2="",AMP!C2="",AMP!D2="",AMP!F2=""),"", IF(AND(AMP!B2="",AMP!C2<>"",AMP!D2<>"",AMP!F2=""),"AMP + ATA MISSING", IF(AND(AMP!B2="",AMP!C2<>"",AMP!D2="",AMP!F2<>""),"AMP + DESCRIPTION MISSING", IF(AND(AMP!B2<>"",AMP!C2<>"",AMP!D2="",AMP!F2=""),"DESCRIPTION + ATA MISSING", IF(AND(AMP!B2<>"",AMP!C2="",AMP!D2="",AMP!F2<>""),"DESCRIPTION + CUST TASK REF MISSING", IF(AND(AMP!B2<>"",AMP!C2="",AMP!D2<>"",AMP!F2=""),"ATA + CUST TASK REF MISSING",""))))))))))))))
D3	D3	=IF(AMP!D3="************** TASK DELETED **************","", IF(OR('AB AMP DATA'!P6="As Req",'AB AMP DATA'!P6="OOP",'AB AMP DATA'!P6="Vendor",'AB AMP DATA'!P6=""),"","Tab "& SHEET('AB AMP DATA'!P6) & " Row "&ROW('AB AMP DATA'!P6) &" As Req, OOP or Vendor must be used"))
E3	E3	=IF(AMP!D3="************** TASK DELETED **************","", IF(AND('AB AMP DATA'!B4=""),"",IF(AND('AB AMP DATA'!P4<>"",'AB AMP DATA'!W4=""),"", IF(AND('AB AMP DATA'!P4="",'AB AMP DATA'!W4<>""),"", IF(AND('AB AMP DATA'!P4="",'AB AMP DATA'!W4=""),"OOP or LC Required",IF(AND('AB AMP DATA'!P4<>"",'AB AMP DATA'!W4<>""),"BOTH OOP AND LC HAVE BEEN USED",""))))))
F3	F3	=IF(AMP!D3="************** TASK DELETED **************","", IF('AB AMP DATA'!R6="","",IF(ISNUMBER('AB AMP DATA'!R6),"","TAB "& SHEET('AB AMP DATA'!R6)&" ROW "&ROW('AB AMP DATA'!R6)& " IS NOT NUMERIC")))
G3	G3	=IF(AMP!D3="************** TASK DELETED **************","", IF('AB AMP DATA'!X6="","",IF(ISNUMBER('AB AMP DATA'!X6),"","TAB "& SHEET('AB AMP DATA'!X6)&" ROW "&ROW('AB AMP DATA'!X6)& " IS NOT NUMERIC")))
H3	H3	=IF(AMP!D3="************** TASK DELETED **************","", IF('AB AMP DATA'!S6="","",IF(ISNUMBER('AB AMP DATA'!S6),"","TAB "& SHEET('AB AMP DATA'!S6)&" ROW "&ROW('AB AMP DATA'!S6)& " IS NOT NUMERIC")))
I3	I3	=IF(AMP!D3="************** TASK DELETED **************","", IF('AB AMP DATA'!Y6="","",IF(ISNUMBER('AB AMP DATA'!Y6),"","TAB "& SHEET('AB AMP DATA'!Y6)&" ROW "&ROW('AB AMP DATA'!Y6)& " IS NOT NUMERIC")))
J3	J3	=IF(AMP!D3="************** TASK DELETED **************","", IF('AB AMP DATA'!T6="","",IF(ISNUMBER('AB AMP DATA'!T6),"","TAB "& SHEET('AB AMP DATA'!T6)&" ROW "&ROW('AB AMP DATA'!T6)& " IS NOT NUMERIC")))
K3	K3	=IF(AMP!D3="************** TASK DELETED **************","", IF('AB AMP DATA'!Z6="","",IF(ISNUMBER('AB AMP DATA'!Z6),"","TAB "& SHEET('AB AMP DATA'!Z6)&" ROW "&ROW('AB AMP DATA'!Z6)& " IS NOT NUMERIC")))
L3	L3	=IF(AMP!D3="************** TASK DELETED **************","", IF('AB AMP DATA'!U6="","",IF(ISNUMBER('AB AMP DATA'!U6),"","TAB "& SHEET('AB AMP DATA'!U6)&" ROW "&ROW('AB AMP DATA'!U6)& " IS NOT NUMERIC")))
M3	M3	=IF(AMP!D3="************** TASK DELETED **************","", IF('AB AMP DATA'!AA6="","",IF(ISNUMBER('AB AMP DATA'!AA6),"","TAB "& SHEET('AB AMP DATA'!AA6)&" ROW "&ROW('AB AMP DATA'!AA6)& " IS NOT NUMERIC")))
N3	N3	=IF(AMP!D3="************** TASK DELETED **************","", IF('AB AMP DATA'!V6="","",IF(ISNUMBER('AB AMP DATA'!V6),"","TAB "& SHEET('AB AMP DATA'!V6)&" ROW "&ROW('AB AMP DATA'!V6)& " IS NOT NUMERIC")))
O3	O3	=IF(AMP!D3="************** TASK DELETED **************","", IF('AB AMP DATA'!AB6="","",IF(ISNUMBER('AB AMP DATA'!AB6),"","TAB "& SHEET('AB AMP DATA'!AB6)&" ROW "&ROW('AB AMP DATA'!AB6)& " IS NOT NUMERIC")))
Q3	Q3	=IF(AMP!D3="************** TASK DELETED **************","", IF(AND('AB AMP DATA'!B6=""),"",IF(AND('AB AMP DATA'!X6<>"",'AB AMP DATA'!Y6="",'AB AMP DATA'!Z6=""),"", IF(AND('AB AMP DATA'!X6="",'AB AMP DATA'!Y6<>"",'AB AMP DATA'!Z6=""),"", IF(AND('AB AMP DATA'!X6="",'AB AMP DATA'!Y6="",'AB AMP DATA'!Z6<>""),"",IF(AND('AB AMP DATA'!X6<>"",'AB AMP DATA'!Y6<>"",'AB AMP DATA'!Z6=""),"TOO MANY CALENDAR INTERVALS",IF(AND('AB AMP DATA'!X6<>"",'AB AMP DATA'!Y6="",'AB AMP DATA'!Z6<>""),"TOO MANY CALENDAR INTERVALS",IF(AND('AB AMP DATA'!X6="",'AB AMP DATA'!Y6<>"",'AB AMP DATA'!Z6<>""),"TOO MANY CALENDAR INTERVALS",IF(AND('AB AMP DATA'!X6<>"",'AB AMP DATA'!Y6<>"",'AB AMP DATA'!Z6<>""),"TOO MANY CALENDAR INTERVALS","")))))))))
R3	R3	=IF(AMP!D3="************** TASK DELETED **************","",IF('AB AMP DATA'!B6="","",IF('AB AMP DATA'!W6<>"","",IF('AB AMP DATA'!P6="AS REQ","",IF('AB AMP DATA'!P6="","",IF(AND('AB AMP DATA'!P6="OOP",OR('AB AMP DATA'!X6<>"",'AB AMP DATA'!Y6<>"",'AB AMP DATA'!Z6<>"",'AB AMP DATA'!AA6<>"",'AB AMP DATA'!AB6<>"")),"","REPEAT REQUIRED"))))))
U3	U3	=IF(AMP!D3="************** TASK DELETED **************","", IF(AND('AB AMP DATA'!B6=""),"",IF(AND(Configuration!B3<>"",Configuration!E3=""),"ATA missing","")))
C4	C4	=IF(AMP!D4="************** TASK DELETED **************","", IF(AND(Configuration!B4="",OR('AB AMP DATA'!M9<>"",'AB AMP DATA'!N9<>"")),"POSITION LINK REQUIRED",""))
D4	D4	=IF(AMP!D4="************** TASK DELETED **************","", IF(OR('AB AMP DATA'!P9="As Req",'AB AMP DATA'!P9="OOP",'AB AMP DATA'!P9="Vendor",'AB AMP DATA'!P9=""),"","Tab "& SHEET('AB AMP DATA'!P9) & " Row "&ROW('AB AMP DATA'!P9) &" As Req, OOP or Vendor must be used"))
E4	E4	=IF(AMP!D4="************** TASK DELETED **************","", IF(AND('AB AMP DATA'!B6=""),"",IF(AND('AB AMP DATA'!P6<>"",'AB AMP DATA'!W6=""),"", IF(AND('AB AMP DATA'!P6="",'AB AMP DATA'!W6<>""),"", IF(AND('AB AMP DATA'!P6="",'AB AMP DATA'!W6=""),"OOP or LC Required",IF(AND('AB AMP DATA'!P6<>"",'AB AMP DATA'!W6<>""),"BOTH OOP AND LC HAVE BEEN USED",""))))))
F4	F4	=IF(AMP!D4="************** TASK DELETED **************","", IF('AB AMP DATA'!R9="","",IF(ISNUMBER('AB AMP DATA'!R9),"","TAB "& SHEET('AB AMP DATA'!R9)&" ROW "&ROW('AB AMP DATA'!R9)& " IS NOT NUMERIC")))
G4	G4	=IF(AMP!D4="************** TASK DELETED **************","", IF('AB AMP DATA'!X9="","",IF(ISNUMBER('AB AMP DATA'!X9),"","TAB "& SHEET('AB AMP DATA'!X9)&" ROW "&ROW('AB AMP DATA'!X9)& " IS NOT NUMERIC")))
H4	H4	=IF(AMP!D4="************** TASK DELETED **************","", IF('AB AMP DATA'!S9="","",IF(ISNUMBER('AB AMP DATA'!S9),"","TAB "& SHEET('AB AMP DATA'!S9)&" ROW "&ROW('AB AMP DATA'!S9)& " IS NOT NUMERIC")))
I4	I4	=IF(AMP!D4="************** TASK DELETED **************","", IF('AB AMP DATA'!Y9="","",IF(ISNUMBER('AB AMP DATA'!Y9),"","TAB "& SHEET('AB AMP DATA'!Y9)&" ROW "&ROW('AB AMP DATA'!Y9)& " IS NOT NUMERIC")))
J4	J4	=IF(AMP!D4="************** TASK DELETED **************","", IF('AB AMP DATA'!T9="","",IF(ISNUMBER('AB AMP DATA'!T9),"","TAB "& SHEET('AB AMP DATA'!T9)&" ROW "&ROW('AB AMP DATA'!T9)& " IS NOT NUMERIC")))
K4	K4	=IF(AMP!D4="************** TASK DELETED **************","", IF('AB AMP DATA'!Z9="","",IF(ISNUMBER('AB AMP DATA'!Z9),"","TAB "& SHEET('AB AMP DATA'!Z9)&" ROW "&ROW('AB AMP DATA'!Z9)& " IS NOT NUMERIC")))
L4	L4	=IF(AMP!D4="************** TASK DELETED **************","", IF('AB AMP DATA'!U9="","",IF(ISNUMBER('AB AMP DATA'!U9),"","TAB "& SHEET('AB AMP DATA'!U9)&" ROW "&ROW('AB AMP DATA'!U9)& " IS NOT NUMERIC")))
M4	M4	=IF(AMP!D4="************** TASK DELETED **************","", IF('AB AMP DATA'!AA9="","",IF(ISNUMBER('AB AMP DATA'!AA9),"","TAB "& SHEET('AB AMP DATA'!AA9)&" ROW "&ROW('AB AMP DATA'!AA9)& " IS NOT NUMERIC")))
N4	N4	=IF(AMP!D4="************** TASK DELETED **************","", IF('AB AMP DATA'!V9="","",IF(ISNUMBER('AB AMP DATA'!V9),"","TAB "& SHEET('AB AMP DATA'!V9)&" ROW "&ROW('AB AMP DATA'!V9)& " IS NOT NUMERIC")))
O4	O4	=IF(AMP!D4="************** TASK DELETED **************","", IF('AB AMP DATA'!AB9="","",IF(ISNUMBER('AB AMP DATA'!AB9),"","TAB "& SHEET('AB AMP DATA'!AB9)&" ROW "&ROW('AB AMP DATA'!AB9)& " IS NOT NUMERIC")))
Q4	Q4	=IF(AMP!D4="************** TASK DELETED **************","", IF(AND('AB AMP DATA'!B9=""),"",IF(AND('AB AMP DATA'!X9<>"",'AB AMP DATA'!Y9="",'AB AMP DATA'!Z9=""),"", IF(AND('AB AMP DATA'!X9="",'AB AMP DATA'!Y9<>"",'AB AMP DATA'!Z9=""),"", IF(AND('AB AMP DATA'!X9="",'AB AMP DATA'!Y9="",'AB AMP DATA'!Z9<>""),"",IF(AND('AB AMP DATA'!X9<>"",'AB AMP DATA'!Y9<>"",'AB AMP DATA'!Z9=""),"TOO MANY CALENDAR INTERVALS",IF(AND('AB AMP DATA'!X9<>"",'AB AMP DATA'!Y9="",'AB AMP DATA'!Z9<>""),"TOO MANY CALENDAR INTERVALS",IF(AND('AB AMP DATA'!X9="",'AB AMP DATA'!Y9<>"",'AB AMP DATA'!Z9<>""),"TOO MANY CALENDAR INTERVALS",IF(AND('AB AMP DATA'!X9<>"",'AB AMP DATA'!Y9<>"",'AB AMP DATA'!Z9<>""),"TOO MANY CALENDAR INTERVALS","")))))))))
R4	R4	=IF(AMP!D4="************** TASK DELETED **************","",IF('AB AMP DATA'!B9="","",IF('AB AMP DATA'!W9<>"","",IF('AB AMP DATA'!P9="AS REQ","",IF('AB AMP DATA'!P9="","",IF(AND('AB AMP DATA'!P9="OOP",OR('AB AMP DATA'!X9<>"",'AB AMP DATA'!Y9<>"",'AB AMP DATA'!Z9<>"",'AB AMP DATA'!AA9<>"",'AB AMP DATA'!AB9<>"")),"","REPEAT REQUIRED"))))))
U4	U4	=IF(AMP!D4="************** TASK DELETED **************","", IF(AND('AB AMP DATA'!B9=""),"",IF(AND(Configuration!B4<>"",Configuration!E4=""),"ATA missing","")))