In each of the four worksheets (one for each week) of this workbook, there is a two column database showing Employee ID and Revenue earned by that employee in that week. Not all employees appear on all sheets and there is no fixed cell reference for any employee on any sheet – therefore employee A001 can be on cell B4 of week 1 sheet but on cell B10 on week 2 sheet.
In the Summary, all unique Employee ID’s have been listed in range B4:B17. The question is “How can the employee wise monthly revenue be computed in range C4:C17”.
I can think of two ways of solving this problem.
Create a Pivot Table from four worksheets (without first copying data from all four worksheets into one). To create a Pivot Table from multiple worksheets, you may refer to my solution at this link.
Write a multi sheet array formula.