Forum Discussion
How to handle rows shifting after updating filtered data
I'd like to be able to update Status (Employee sheet > Column C) from "Active" to "Not Active" and not offset/shift/alter the data in Columns C, D, E, F, etc. in my other sheets. So, if I have an EmployeeTable with 5 employees and their respective salaries
EmployeeTable
| ID | Employee Name | Status |
| 001 | A | Active |
| 002 | B | Active |
| 003 | C | Active |
004 | D | Active |
005 | E | Active |
SalaryTable
| ID | Employee Name | Weekly Salary |
| 001 | A | $10 |
| 002 | B | $20 |
| 003 | C | $30 |
| 004 | D | $40 |
| 005 | E | $50 |
And the following week I have to mark employee B as "Not Active" because he resigned
| ID | Employee Name | Status |
| 001 | A | Active |
| 002 | B | Not Active |
| 003 | C | Active |
| 004 | D | Active |
| 005 | E | Active |
Not have it mess up everyone else's salaries
| ID | Employee Name | Weekly Salary |
| 001 | A | $10 |
| 003 | C | $20 |
| 004 | D | $30 |
| 005 | E | $40 |
| $50 |
In the attached file, the formula for Days Worked is:
=SUMPRODUCT((AttendanceTable[ID]=[@ID])*
(AttendanceTable[RH-Thursday]:AttendanceTable[TH-Wednesday]>0))
For Regular Hours, the formula is:
=INDEX(MMULT(--AttendanceTable[RH-Thursday]:AttendanceTable[TH-Wednesday],
{1;0;0;1;0;0;1;0;0;1;0;0;1;0;0;1;0;0;1;0;0}),
MATCH([@ID],AttendanceTable[ID],0))
For Regular Hour Salary, the formula is:
=[@[Regular Hours]]*
LOOKUP([@ID],SalaryTable[ID]:SalaryTable[Regular Hour])
The succeeding formulas follow the same logic as the foregoing.
It doesn't seem to be working as expected. I'm guessing it has to do with columns from other tables not containing similar formulas. I'll look into it further.
Thanks for helping out.