(Not Done) Real Esate Taxes.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
68 | 2014 C&L | WIGHTMAN JOSEPH S JR/ CHERYL ANN | 4/30/2014 | 01200705 | $441.73 | $450.75 | $495.83 | $6,250 | ||||
69 | 2014 SCHOOL | WIGHTMAN JOSEPH S JR/CHERYL ANN | 12/17/2014 | 1200708 | $101.51 | $103.58 | $113.94 | $6,250 | ||||
70 | Payment 1 | 7/31/2014 | 34.52 | FACE | ||||||||
71 | Payment 2 | 10/22/2014 | 37.98 | FACE | ||||||||
72 | Payment 3 | 12/17/2014 | 37.98 | FACE | ||||||||
73 | Total | Paid In Full | ||||||||||
Real Estate History |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D68 | D68 | =INDEX('C&L 2014'!$B$2:$N$5595,MATCH($A$2,'C&L 2014'!$N$2:$N$5595,0),1) |
E68 | E68 | =INDEX('C&L 2014'!$G$2:$N$5595,MATCH($A$2,'C&L 2014'!$N$2:$N$5595,0),1) |
F68 | F68 | =INDEX('C&L 2014'!$H$2:$N$5595,MATCH($A$2,'C&L 2014'!$N$2:$N$5595,0),1) |
G68 | G68 | =INDEX('C&L 2014'!$I$2:$N$5595,MATCH($A$2,'C&L 2014'!$N$2:$N$5595,0),1) |
H68 | H68 | =INDEX('C&L 2014'!$M$2:$N$5595,MATCH($A$2,'C&L 2014'!$N$2:$N$5595,0),1) |
I68 | I68 | =IF(INDEX('C&L 2014'!$F$2:$N$5595,MATCH($A$2,'C&L 2014'!$N$2:$N$5595,0),1)="","",INDEX('C&L 2014'!$F$2:$N$5595,MATCH($A$2,'C&L 2014'!$N$2:$N$5595,0),1)) |
J68 | J68 | =IF(INDEX('C&L 2014'!$N$2:$R$7003,MATCH($A$2,'C&L 2014'!$N$2:$N$7003,0),5)="","",IF(INDEX('C&L 2014'!$N$2:$R$7003,MATCH($A$2,'C&L 2014'!$N$2:$N$7003,0),5)=" ","",INDEX('C&L 2014'!$N$2:$R$7003,MATCH($A$2,'C&L 2014'!$N$2:$N$7003,0),5))) |
B68 | B68 | =IF(INDEX('C&L 2014'!$A$2:$N$5595,MATCH($A$2,'C&L 2014'!$N$2:$N$5595,0),1)="","",INDEX('C&L 2014'!$A$2:$N$5595,MATCH($A$2,'C&L 2014'!$N$2:$N$5595,0),1)) |
B69 | B69 | =IF(INDEX('SK 2014'!$A$2:$T$5393,MATCH($A$2,'SK 2014'!$T$2:$T$5393,0),1)="","UNPAID",INDEX('SK 2014'!$A$2:$T$5393,MATCH($A$2,'SK 2014'!$T$2:$T$5393,0),1)) |
D69 | D69 | =INDEX('SK 2014'!$B$2:$T$5393,MATCH($A$2,'SK 2014'!$T$2:$T$5393,0),1) |
E69 | E69 | =INDEX('SK 2014'!$T$2:$X$5393,MATCH($A$2,'SK 2014'!$T$2:$T$5393,0),5) |
F69 | F69 | =INDEX('SK 2014'!$T$2:$Y$5393,MATCH($A$2,'SK 2014'!$T$2:$T$5393,0),6) |
G69 | G69 | =INDEX('SK 2014'!$T$2:$Z$5393,MATCH($A$2,'SK 2014'!$T$2:$T$5393,0),7) |
H69 | H69 | =INDEX('SK 2014'!$S$2:$T$5393,MATCH($A$2,'SK 2014'!$T$2:$T$5393,0),1) |
I69 | I69 | =IF(INDEX('SK 2014'!$F$2:$T$5393,MATCH($A$2,'SK 2014'!$T$2:$T$5393,0),1)="","",INDEX('SK 2014'!$F$2:$T$5393,MATCH($A$2,'SK 2014'!$T$2:$T$5393,0),1)) |
D70 | D70 | =IF(INDEX('SK 2014'!$H$2:$T$6865,MATCH($A$2,'SK 2014'!$T$2:$T$6865,0),1)="","",INDEX('SK 2014'!$H$2:$T$6865,MATCH($A$2,'SK 2014'!$T$2:$T$6865,0),1)) |
E70 | E70 | =IF(C70="","",IF(C70>0,"FACE")) |
D71 | D71 | =IF(INDEX('SK 2014'!$J$2:$T$6865,MATCH($A$2,'SK 2014'!$T$2:$T$6865,0),1)="","",INDEX('SK 2014'!$J$2:$T$6865,MATCH($A$2,'SK 2014'!$T$2:$T$6865,0),1)) |
E71 | E71 | =IF(D70="","",IF(C69="UNPAID","",IF(D71="","",IF(D70>=(D71-0.03),"FACE",IF(D70<D71,"PENALTY"))))) |
D72 | D72 | =IF(INDEX('SK 2014'!$L$2:$T$6865,MATCH($A$2,'SK 2014'!$T$2:$T$6865,0),1)="","",INDEX('SK 2014'!$L$2:$T$6865,MATCH($A$2,'SK 2014'!$T$2:$T$6865,0),1)) |
E72 | E72 | =IF(D71="","",IF(C69="UNPAID","",IF(D72="","",IF(D70>SUM(D72-0.03),"FACE",IF(D70<D72,"PENALTY"))))) |
D73 | D73 | =IF(D70="","",IF(SUMIF(C70:C72,"<>",D70:D72)>=F69,"Paid In Full",IF(SUMIF(C70:C72,"<>",D70:D72)<0.03,"Paid In Full",IF(F69>SUMIF(C70:C72,"<>",D70:D72),SUMIF(C70:C72,"<>",D70:D72)-F69,"help")))) |
E73 | E73 | =IF($D73>=0,"","SHORT in Face") |
C68 | C68 | =IF(INDEX('C&L 2014'!$C$2:$N$5595,MATCH($A$2,'C&L 2014'!$N$2:$N$5595,0),1)="","UNPAID",INDEX('C&L 2014'!$C$2:$N$5595,MATCH($A$2,'C&L 2014'!$N$2:$N$5595,0),1)) |
C69 | C69 | =IF(INDEX('SK 2014'!$C$2:$T$6897,MATCH($A$2,'SK 2014'!$T$2:$T$6897,0),1)="","UNPAID",IF(D73="Paid In Full",C72,IF(D73="",INDEX('SK 2014'!$C$2:$T$6897,MATCH($A$2,'SK 2014'!$T$2:$T$6897,0),1),"Short"))) |
C70 | C70 | =IF(INDEX('SK 2014'!$G$2:$T$6865,MATCH($A$2,'SK 2014'!$T$2:$T$6865,0),1)="","",INDEX('SK 2014'!$G$2:$T$6865,MATCH($A$2,'SK 2014'!$T$2:$T$6865,0),1)) |
C71 | C71 | =IF(INDEX('SK 2014'!$I$2:$T$6865,MATCH($A$2,'SK 2014'!$T$2:$T$6865,0),1)="","",INDEX('SK 2014'!$I$2:$T$6865,MATCH($A$2,'SK 2014'!$T$2:$T$6865,0),1)) |
C72 | C72 | =IF(INDEX('SK 2014'!$K$2:$T$6865,MATCH($A$2,'SK 2014'!$T$2:$T$6865,0),1)="","",INDEX('SK 2014'!$K$2:$T$6865,MATCH($A$2,'SK 2014'!$T$2:$T$6865,0),1)) |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
'SK 2014'!CSV49XLS_6 | ='SK 2014'!$A$2:$Z$4187 | B69 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
D73 | Cell Value | <0 | text | NO |
should show
2014 C&L | WIGHTMAN JOSEPH S JR/ CHERYL ANN | 4/30/2014 | 01200705 | $441.73 | $450.75 | $495.83 | $6,250 | ||
2014 SCHOOL | WIGHTMAN JOSEPH S JR/CHERYL ANN | 12/17/2014 | 1200708 | $101.51 | $103.58 | $113.94 | $6,250 | ||
Payment 1 | 7/31/2014 | 34.52 | FACE | ||||||
Payment 2 | 10/22/2014 | 37.98 | PENALTY | ||||||
Payment 3 | 12/17/2014 | 37.98 | PENALTY | ||||||
Total | Paid In Full |