| Automated 2022 Tax Certification Form.xlsm | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 26 | SCHOOL 2022 | July/Aug | Sept/Oct | Nov/Dec | |||
| 27 | Bill #: | 01700102 | Discount (-2%) | Face: | Penalty (+10%) | ||
| 28 | 122 | #VALUE! | $1,170.89 | $1,194.79 | $1,314.27 | ||
| 29 | Date Paid: | #VALUE! | HomeStead Amount | HomeStead | Assessed Value | ||
| 30 | $100.60 | NO | 31,450 | ||||
| 31 | PARTIALS | DATE | #VALUE! | <- SHORT IN FACE | |||
| 32 | PAYMENT #1 | 7/18/2022 #1 | $398.27 | FACE | |||
| 33 | PAYMENT #2 | 09/08/2022 | $398.26 | FACE | |||
| 34 | PAYMENT #3 | ||||||
Tax Cert Form 2022-2021 | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A28 | A28 | =INDEX('[Rle_School 2022.xls]Real Estate'!$E:$U,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),1) |
| C28 | C28 | =INDEX('[Rle_School 2022.xls]Real Estate'!$U:$AL,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),11) |
| D28 | D28 | =INDEX('[Rle_School 2022.xls]Real Estate'!$U:$AL,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),12) |
| E28 | E28 | =INDEX('[Rle_School 2022.xls]Real Estate'!$U:$AL,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),13) |
| B27 | B27 | =INDEX('[Rle_School 2022.xls]Real Estate'!$B:$U,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),1) |
| B28 | B28 | =IF(B29="SHORT","SHORT",IF(B29="","UNPAID","PAID")) |
| B29 | B29 | =IF(D28<=SUM(D32,D33,D34),C34,IF(D28>D31,"SHORT",IF(INDEX('[Rle_School 2022.xls]Real Estate'!$C:$U,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),1)="","",INDEX('[Rle_School 2022.xls]Real Estate'!$C:$U,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),1)))) |
| E30 | E30 | =INDEX('[Rle_School 2022.xls]Real Estate'!$T$2:$U$7008,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U$2:$U$7008,0),1) |
| D30 | D30 | =INDEX('[Rle_School 2022.xls]Real Estate'!$U:$AI,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),15) |
| D31 | D31 | =IF(SUM(D32:D34)=0,"",IF((D32:D34)<D28,SUM(D32:D34)-(D28))) |
| C32 | C32 | =IF(INDEX('[Rle_School 2022.xls]Real Estate'!$H:$U,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),1)="","",INDEX('[Rle_School 2022.xls]Real Estate'!$H:$U,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),1)) |
| D32 | D32 | =IF(INDEX('[Rle_School 2022.xls]Real Estate'!$I:$U,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),1)="","",INDEX('[Rle_School 2022.xls]Real Estate'!$I:$U,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),1)) |
| E32 | E32 | =IF(C32="","", "FACE") |
| C33 | C33 | =IF(INDEX('[Rle_School 2022.xls]Real Estate'!$J:$AN,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$AN:$AN,0),1)="","",INDEX('[Rle_School 2022.xls]Real Estate'!$J:$AN,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$AN:$AN,0),1)) |
| D33 | D33 | =IF(INDEX('[Rle_School 2022.xls]Real Estate'!$K:$AN,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$AN:$AN,0),1)="","",INDEX('[Rle_School 2022.xls]Real Estate'!$K:$AN,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$AN:$AN,0),1)) |
| E33:E34 | E33 | =IF(C33="","",IF($D$52<=D33,"FACE",IF($D$52>D33,"PENALTY"))) |
| C34 | C34 | =IF(INDEX('[Rle_School 2022.xls]Real Estate'!$L:$AO,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$AO:$AO,0),1)="","",INDEX('[Rle_School 2022.xls]Real Estate'!$L:$AO,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$AO:$AO,0),1)) |
| D34 | D34 | =IF(INDEX('[Rle_School 2022.xls]Real Estate'!$M:$AO,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$AO:$AO,0),1)="","",INDEX('[Rle_School 2022.xls]Real Estate'!$M:$AO,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$AO:$AO,0),1)) |
Should show 398.26
| SCHOOL 2022 | July/Aug | Sept/Oct | Nov/Dec | |
| Bill #: | 01700102 | Discount (-2%) | Face: | Penalty (+10%) |
| 122 | #VALUE! | $1,170.89 | $1,194.79 | $1,314.27 |
| Date Paid: | #VALUE! | HomeStead Amount | HomeStead | Assessed Value |
| $100.60 | NO | 31,450 | ||
| PARTIALS | DATE | #VALUE! | <- SHORT IN FACE | |
| PAYMENT #1 | 7/18/2022 #1 | $398.27 | FACE | |
| PAYMENT #2 | 09/08/2022 | $398.26 | FACE | |
| PAYMENT #3 |
