Human Resource Example: Hires & Terminations in a Month

Description
Forget Excel PivotTables. Get summary of hires & terminations in a month from staff master sheet using Dynamic Array (DA) Excel Worksheet functions. This approach leverages Dynamic Arrays to automatically update the summary as new data is added to your master sheet. This is helpful template for human resource professionals and anyone else who is interested in getting summary result from master sheet of huge date. The master sheet could be expanded with columns of interest to you. Below is breakdown of this template:



Function Purpose 
1. Sort fx: Sorts 4th column, i.e., "Hire Date" column from oldest to newest date 
2. Filter fx: Filters data i.e., "tblStaff" from master sheet as per multiple criteria present in cells A2:C23. Index fx: Returns value at the intersection of row & column from master sheet
4. Sequence fx: Sequence returns row number from master sheet 
5. Rows fx: Returns number of rows in master sheet 
6. Match fx: Matches "Headers" (i.e., Country, Name, Designation, Hire Date) in summary sheet to master sheet