Forum Discussion
How to handle rows shifting after updating filtered data
Please specify in which cells do you need help with and what are your expected results in each of those cells.
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.
