Spill functions with other functions

MiniFav

Board Regular
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.

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

jasonb75

Well-known Member
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

MrExcel MVP, Moderator
It looks like you want something like:

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

MiniFav

Board Regular
It looks like you want something like:

Excel Formula:
``=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

Board Regular
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
CDEFGHIJKLMNOPQRSTUVW
1Task 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) expectedDays(T) Not NumericDays(R) Not NumericMonths(T) Not NumericMonths(R) Not NumericYears(T) Not NumericYears(R) Not NumericHours(T) Not NumericHours(R) Not NumericCycles(T) Not NumericCycles(R) Not NumericCan 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 missingPOSITION 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
RangeFormula
D2D2=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"))
E2E2=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",""))))))
F2F2=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")))
G2G2=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")))
H2H2=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")))
I2I2=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")))
J2J2=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")))
K2K2=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")))
L2L2=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")))
M2M2=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")))
N2N2=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")))
O2O2=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:P4P2=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","")))))))))
Q2Q2=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","")))))))))
R2R2=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:S4S2=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:T4T2=IF(AMP!D2="************** TASK DELETED **************","", IF(COUNTIF(Configuration!B:B, Configuration!B2)>1, "Duplicated Position: " & Configuration!B2, ""))
U2U2=IF(AMP!D2="************** TASK DELETED **************","", IF(AND('AB AMP DATA'!B2=""),"",IF(AND(Configuration!B2<>"",Configuration!E2=""),"ATA missing","")))
V2:V4V2=IF(AMP!D2="************** TASK DELETED **************","", IF(Configuration!D2="","",IF(Configuration!D2=Configuration!B2,"POSITION CANNOT BE ITS OWN PARENT","")))
W2:W4W2=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",""))))))))))))))
D3D3=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"))
E3E3=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",""))))))
F3F3=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")))
G3G3=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")))
H3H3=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")))
I3I3=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")))
J3J3=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")))
K3K3=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")))
L3L3=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")))
M3M3=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")))
N3N3=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")))
O3O3=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")))
Q3Q3=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","")))))))))
R3R3=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"))))))
U3U3=IF(AMP!D3="************** TASK DELETED **************","", IF(AND('AB AMP DATA'!B6=""),"",IF(AND(Configuration!B3<>"",Configuration!E3=""),"ATA missing","")))
D4D4=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"))
E4E4=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",""))))))
F4F4=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")))
G4G4=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")))
H4H4=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")))
I4I4=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")))
J4J4=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")))
K4K4=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")))
L4L4=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")))
M4M4=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")))
N4N4=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")))
O4O4=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")))
Q4Q4=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","")))))))))
R4R4=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"))))))
U4U4=IF(AMP!D4="************** TASK DELETED **************","", IF(AND('AB AMP DATA'!B9=""),"",IF(AND(Configuration!B4<>"",Configuration!E4=""),"ATA missing","")))

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,138
Messages
5,835,619
Members
430,371
Latest member
contentment

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back