Spill functions with other functions

MiniFav

Board Regular
Joined
Mar 10, 2020
Messages
59
Office Version
  1. 365
Platform
  1. Windows
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.

InformationSpill function to show duplicateFormula
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","")
3BCust Ref 3B is Duplicated=IF(COUNTIF($A$2:$A$10,$A7)>1,"Cust Ref "&$A7&" is Duplicated","")
3BCust 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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 2, 2008
Messages
38,790
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It looks like you want something like:

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

MiniFav

Board Regular
Joined
Mar 10, 2020
Messages
59
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 10, 2020
Messages
59
Office Version
  1. 365
Platform
  1. Windows
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
C2:C3C2=IF(AMP!D2="************** TASK DELETED **************","", IF(AND(Configuration!B2="",OR('AB AMP DATA'!M5<>"",'AB AMP DATA'!N5<>"")),"POSITION LINK REQUIRED",""))
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","")))
C4C4=IF(AMP!D4="************** TASK DELETED **************","", IF(AND(Configuration!B4="",OR('AB AMP DATA'!M9<>"",'AB AMP DATA'!N9<>"")),"POSITION LINK REQUIRED",""))
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","")))
 
Master Excel Bundle

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.

Forum statistics

Threads
1,164,139
Messages
5,835,626
Members
430,372
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top