Forum Discussion

emilioramirezz's avatar
emilioramirezz
Copper Contributor
Oct 31, 2019

How to handle rows shifting after updating filtered data

Hi! Recently, Twifoo helped me return filtered data from an employee list to use it for other administrative purposes. However, I've added additional columns to the other sheets that use the filtered data and now, everytime I update the employee list, my other sheets shift up a row and the rest of the columns' data gets out of sync.

 

The employee list, which acts as the source data for all employees, is in the Employees sheet and includes 5 columns: ID, Employee Name, Status, Shift, and Work Days.

 

All other sheets use the ID column to bring only "Active" employees with this formula:

=IFERROR(INDEX(EmployeeTable[ID],AGGREGATE(15,6,EmployeeTable[ID]/(EmployeeTable[Status]="Active"),ROW()-1)),"")

 

After that, I use the ID column to bring up the corresponding Employee Names with  this formula:

=IF([ID]="","",LOOKUP([ID],EmployeeTable[ID]:EmployeeTable[Employee Name]))

 

Apart from those two columns, all other columns are affected when I mark an employee as "Non Active".

 

How can I improve my formulas or my column structure to prevent this shifting data? I'm attaching an example workbook with the same structure mentioned.

 

Thanks

 

 

 

 

excel
Excel on Mac
Formulas and Functions

4 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Nov 07, 2019

    emilioramirezz 

    Please specify in which cells do you need help with and what are your expected results in each of those cells. 

    • emilioramirezz's avatar
      emilioramirezz
      Copper Contributor
      Nov 07, 2019

      Twifoo

       

      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

       

      IDEmployee Name

      Status

      001AActive
      002BActive
      003CActive

      004

      		DActive

      005

      		EActive

       

      SalaryTable

       

      IDEmployee NameWeekly Salary
      001A$10
      002B$20
      003C$30
      004D$40
      005E$50

       

      And the following week I have to mark employee B as "Not Active" because he resigned

       

      IDEmployee NameStatus
      001AActive
      002BNot Active
      003CActive
      004DActive
      005EActive

       

      Not have it mess up everyone else's salaries

       

      IDEmployee NameWeekly Salary
      001A$10
      003C$20
      004D$30
      005E$40
        $50
      • Twifoo's avatar
        Twifoo
        Silver Contributor
        Nov 09, 2019

        emilioramirezz 

        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. 

Resources