Need help with the macro please. Whenever, I click on those numbers in the Summary tab, like E6 (with value of -1), it will link me to the Raw Data sheet and then filtered the data show in image 3 (where it filtered column B, C and D) with this I would be able to see who is the employee for the said variance.
Image 1
Image 2
Image 3
Image 1
HeadCount Audit.xlsm | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
4 | |||||||
5 | Supervisor | HC - Active Employee | Resigned Employee | Other Source Active | Variance HC vs Other Source | ||
6 | Leo | 2 | 2 | 3 | -1 | ||
7 | Patrick | 5 | 0 | 4 | 1 | ||
8 | Total | 7 | 2 | 7 | |||
9 | |||||||
10 | |||||||
11 | |||||||
Summary |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B6:B7 | B6 | =COUNTIFS('Raw Data'!$B:$B,Summary!$A6,'Raw Data'!$C:$C,"Active") |
C6:C7 | C6 | =COUNTIFS('Raw Data'!$B:$B,Summary!$A6,'Raw Data'!$C:$C,"Resigned") |
D6:D7 | D6 | =COUNTIFS('Raw Data'!$B:$B,Summary!$A6,'Raw Data'!$D:$D,"Active") |
E6:E7 | E6 | =B6-D6 |
B8:D8 | B8 | =SUM(B6:B7) |
Image 2
HeadCount Audit.xlsm | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Employee Name | Supervisor | Status | Other Source File | ||
2 | Andrea | Leo | Active | Active | ||
3 | Clair | Leo | Active | Active | ||
4 | Mike | Leo | Resigned | Resigned | ||
5 | Tom | Leo | Resigned | Active | ||
6 | Jordan | Patrick | Active | Active | ||
7 | Michelle | Patrick | Active | Active | ||
8 | Samantha | Patrick | Active | Resigned | ||
9 | Sarah | Patrick | Active | Active | ||
10 | Daniel | Patrick | Active | Active | ||
Raw Data |
Image 3
HeadCount Audit.xlsm | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Employee Name | Supervisor | Status | Other Source File | ||
2 | Andrea | Leo | Active | Active | ||
3 | Clair | Leo | Active | Active | ||
5 | Tom | Leo | Resigned | Active | ||
11 | ||||||
12 | ||||||
13 | ||||||
14 | ||||||
15 | ||||||
Raw Data |