Looking for some assistance here as I rack my brain for a solution.
I have a Scorecard workbook that upon selection of the employee name and month - I want the statistics to update per employee and month.
The data I am referencing is in a separate Audit workbook and is listed by employees in Column A, but monthly data is broken out per month in multiple columns (i.e B, E, H, K, ETC.)
What I am trying to index is the data in columns B, E, H, etc. depending on the employee name in the Scorecard matching the employee name in column A of the Audit log AND the month in the Scorecard matching the month in Row 1 in the Audit log. So formula speaking it's something like this
Formula:
INDEX('Audit'!B:B,AND(MATCH(Scorecard!C3,'Audit'!A:A,0),(MATCH(Scorecard!I3,'Audit'!1:1,0))
For the sake of argument here, pretend Audit and Scorecard are separate files NOT worksheets like how i have written this.
Except where B is I need this to be the multiple columns I mentioned, so that whatever month and employee is I can return the correct result (i.e. Employee = Sally, Month = January, in the Audit log, Sally's January results would be found in cell B2)
Index is just what I am semi familiar with so I tried it. I also tried doing a PivotTable using the data in the Audit log but it was not calculating the true data from the source.
Help please! Open to suggestions/tips
Bookmarks