| Complaints Tracker - FY21.xlsm | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | N | ||||
| 1 | Days Open | Counseling Days Remaining | ADR Days Remaining | iComplaints/Case # | Aggrieved Person (Name) | Employment Status | Date of Initial Contact | Assigned Counselor | Status of Counseling | Date of Initial Interview | Counseling Extension (Y/N) | Withdrawal (Y/N) | ||||
| 2 | 48 | -18 | 42 | James Brown | bottle | unemployed | 44044 | Roberts, Michael | Open | 6/16/20 | N | Y | ||||
| 3 | 337 | -307 | -247 | Jane Doe | war | laithworker | 43738 | Gremillion, Heather | Closed | 6/19/20 | N | N | ||||
| 4 | 299 | -269 | -209 | Louise Jefferson | can | thief | 43776 | Medina, Pete | Closed | 6/18/20 | N | N | ||||
| 5 | 263 | -233 | #VALUE! | Lucille Ball | care | secretary | 43832 | |||||||||
| 6 | 90 | -60 | #VALUE! | Michael Jordan | shoe | hoe | 43894 | |||||||||
| 7 | ||||||||||||||||
| 8 | ||||||||||||||||
| 9 | ||||||||||||||||
Counselings | ||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A2:A9 | A2 | =IF(H2="", "", IF($N2="Y", (O2-$H2), IF(P2="Y", (Q2-H2), IF(Y2="Y", (Z2-H2), TODAY()-H2)))) |
| B2:B9 | B2 | =IF(A2="","",SUM(SUM(IF(L2="y",H2+90,H2+30)-H2)-A2)) |
| C2:C9 | C2 | =IF(A2="","",SUM(SUM(IF(W2="y",H2+90," ")-H2)-A2)) |
| E2:H9 | E2 | =IFERROR(INDEX(Contacts!B$2:B$100,AGGREGATE(15,6,(ROW(Contacts!$M$2:$M$100)-ROW(Contacts!$M$2)+1)/(Contacts!$M$2:$M$100="y"),ROWS(B$2:B2))),"") |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| B2:B371 | Expression | =$B2<10 | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| I:I | List | =DropDown!$F$2:$F$11 |
| J:J | List | =DropDown!$E$2:$E$3 |
| L1:L9 | List | =DropDown!$C$2:$C$3 |
| N1:N9 | List | =DropDown!$C$2:$C$3 |
Hi,
I have done a formula to get information automatically generated based on the content of another sheet. Is there a way to have the information in the rest of the row STAY with the generated information when I do a filter? Or should I just warn everyone not to filter? I just expected the information to expand the selection but it seems to just affect the cells right of the formula differently.
