I need help with a Error Spot Check excel document for checking errors in processing.
Each month I have to check a agents work for errors.
When checking I have a Userform that will fill row A through G per check.
Column C and G are import as:
- Column C: Logs the date the agent did the process
- Column G: Will log the Reason for the Error if there is one
<tbody>
</tbody>
I only check for errors 4 weeks (5 business days per week) out of a month so I have a formula area on the same page starting in columns P6 and Q6 that will determine if I should start checking the first 4 weeks or the last 4 weeks of the month that looks like so:
<tbody>
</tbody>
If the above Booleen is True I will start checking on the 1st day of the first week. If not it will be the first day of the second week.
Finally I have the Error log in Columns I through N that list the matching error reason and sums up each error for for each error type and week but I have to update this manually by hand as I don't know a formula that would match the errors and sum them up by week. This is the Error log:
<tbody>
</tbody>
I need a formula to place in each of the error count cells above that will:
- First check Column C for the date processed to determine if the erorr (If One) will go within that week
- Secondly it will check that same row in Column F to determin if Yes there is an error and if so...
- Third it will check column G to match the reason for the error and..
- Lastly if the error matches the erorr phrase on the left in column I within the range of the Error type, Sum it up with all the matching errors within the week.
A formula to put in each cell would seem more convenient as I don't know if a macro would be a easy go for this.
I have the spreadsheet but didnt see a way to attach it.
Any help here would be greatly appreciated. Thanks in advance.
Each month I have to check a agents work for errors.
When checking I have a Userform that will fill row A through G per check.
Column C and G are import as:
- Column C: Logs the date the agent did the process
- Column G: Will log the Reason for the Error if there is one
| A | B | C | D | E | F | G | |
| 5 | Spot Check Date | Checked By | Date Processed | ID Number | Work Type | Error | Reason for the Error |
| 6 | 10/30/2013 - 4:07 PM | C.M | 10/1/2013 | 657327588 | COCC | No | |
| 7 | 10/30/2013 - 4:07 PM | C.M | 10/7/2013 | 657339594 | COCC | Yes | COCC less than 63 days |
<tbody>
</tbody>
I only check for errors 4 weeks (5 business days per week) out of a month so I have a formula area on the same page starting in columns P6 and Q6 that will determine if I should start checking the first 4 weeks or the last 4 weeks of the month that looks like so:
| P | Q | Formula in Column Q |
| 1st of Current Month: | 10/1/2013 | =C6-DAY(C6)+1 |
| First Day of Month: | 2 | =WEEKDAY((DATEVALUE(MONTH(Q6)&"/1/"&YEAR(Q6))),2) |
| Days in Week: | 7 | |
| Count First Week? | TRUE | =IF(Q7>=3,FALSE,TRUE) |
<tbody>
</tbody>
If the above Booleen is True I will start checking on the 1st day of the first week. If not it will be the first day of the second week.
Finally I have the Error log in Columns I through N that list the matching error reason and sums up each error for for each error type and week but I have to update this manually by hand as I don't know a formula that would match the errors and sum them up by week. This is the Error log:
| I | J | K L M | N | |||
| 6 | INDIVIDUAL ASR QA BEHAVIOR SUMMARY | Error Count | ||||
| 7 | week1 | week2 | week3 | week4 | Total | |
| 8 | Cancellations (CXL) | |||||
| 9 | Billing date not removed | |||||
| 10 | Wrong inactive date | 1 | 1 | 2 | ||
| 11 | Paid through date incorrect | 1 | 1 | |||
| 12 | Cancelled Plan in error | |||||
| 13 | Cancelled wrong plan | |||||
| 14 | Confirmation email not sent | |||||
| 15 | Cancellation document not attached | |||||
| 16 | Wrong document attached as cancellation | |||||
| 17 | Did not cancel the plan | |||||
| and so on.. | Did not cancel dependents | |||||
| Did not check claim status | ||||||
| Checked claim status with wrong entity | ||||||
| Enrollment fee not refunded where appropriate or approved | ||||||
| Refund not set to pay | ||||||
| Refund date not entered | ||||||
| Refund amount incorrect | ||||||
| Refund Update tracking field not set (YES) (NO) | ||||||
| Refund Provided date/info not removed | ||||||
| OLE notes missing | ||||||
| Did not forward template to correct queue(s) | ||||||
| Total | ||||||
| Policy Change Requests (PCR or MAG) | ||||||
| Did not process upgrade/downgrade | ||||||
| Did not change active/inactive date | ||||||
| Wrong billing date | ||||||
| Did not process correct plan | ||||||
| Did not add dependent information | ||||||
| Did not remove dependents | ||||||
| Did not refund premium difference | ||||||
| Did not transfer funds to new plan | ||||||
| Did not transfer correct amount | ||||||
| Did not pro-rate member | ||||||
| Did not attach request to member file | ||||||
| OLE notes missing | ||||||
| Total | ||||||
| Letters of Membership (LOM) | ||||||
| LOC not issued | ||||||
| Wrong inactive date | ||||||
| Wrong effective date | ||||||
| Wrong member ID# | ||||||
| Wrong member information | ||||||
| Did not mail/email/fax to member as requested | ||||||
| Did not attach LOC to OLE | 1 | 1 | ||||
| OLE notes missing | ||||||
| Total | 1 | 1 | ||||
| Certificates of Creditable Coverage (COCC) | ||||||
| Did not attach to member file | ||||||
| OLE notes missing | 1 | 1 | ||||
| Did not forward to carrier | ||||||
| Did not issue letter | ||||||
| COCC less than 63 days | 1 | 1 | ||||
| COCC more than 63 days | ||||||
| Total | 1 | 1 | 2 |
<tbody>
</tbody>
I need a formula to place in each of the error count cells above that will:
- First check Column C for the date processed to determine if the erorr (If One) will go within that week
- Secondly it will check that same row in Column F to determin if Yes there is an error and if so...
- Third it will check column G to match the reason for the error and..
- Lastly if the error matches the erorr phrase on the left in column I within the range of the Error type, Sum it up with all the matching errors within the week.
A formula to put in each cell would seem more convenient as I don't know if a macro would be a easy go for this.
I have the spreadsheet but didnt see a way to attach it.
Any help here would be greatly appreciated. Thanks in advance.
Last edited:
