| Column A | Column B | Column G | Column H | Column P | |
| 1 | Report # | Status | Name | Employee ID # | # of Reports |
| 2 | 1-1 | APPROVED | Adam A | 11 | 1 |
| 3 | 1-2 | APPROVED | Charlie C | 22 | 1 |
| 4 | 2-1 | APPROVED | Bravo B | 33 | 1 |
| 5 | 3-1 | APPROVED | Charlie C | 22 | 2 |
| 6 | 4-1 | VOIDED | Adam A | 11 | VOIDED |
| 7 | 4-2 | APPROVED | Adam A | 11 | 2 |
| 8 | 4-3 | APPROVED | Adam A | 11 | 3 |
| 9 | 5-1 | PENDING | Bravo B | 33 | 2 |
<tbody>
</tbody>
(This is a TABLE)
Hello and THANK YOU in advance,
Column 'P' keeps a continuous count of how many reports the employee files not including the ones that were voided. I added Column P after I added all the data. I put the formula into Cell P2, dragged it down to the last entry and HURRAY!!! IT WORKS GREAT..., almost
When I add new data into the next blank row (Row10+) it changes the end criteria range (for the old last row and all added rows) to the last cell in the table instead of it's own cell. Instead of giving me a continuous count for each employee it starts giving me the total count for each employee.
Before:
Cell P8 =IF(B8="VOIDED","VOIDED",COUNTIFS(B$2:B8,"<>VOIDED",H$2:H8,H8)
Cell P9 =IF(B9="VOIDED","VOIDED",COUNTIFS(B$2:B9,"<>VOIDED",H$2:H9,H9)
After:
Cell P8 =IF(B8="VOIDED","VOIDED",COUNTIFS(B$2:B8,"<>VOIDED",H$2:H8,H8)
Cell P9 =IF(B9="VOIDED","VOIDED",COUNTIFS(B$2:B10,"<>VOIDED",H$2:H10,H9) **Error Inconsistent Calculated Column Formula
Cell P10 =IF(B10="VOIDED","VOIDED,COUNTIFS(B$2:B10,"<>VOIDED",H$2:H10,H10)
EG:
Row 10 David D=1
Row 10 David D=2
Row 11 David D=2
I know I can just extend the formula after I add new data, but I am designing this for a dozen other offices who will fill out the sheet for data pertaining to their specific office and EXCEL bewilders them (just like VBA bewilders me--hint-hint).
P.S. There re a lot more columns (A:BE) and worksheets (10). 1/3 of the columns have formulas and all the worksheets pull data from this worksheet/table. I intend to lock and protect the formula columns and worksheets.
Thank you so much. MREXCEL RULES!
