Formula for getting worksheet names based on pivot table results

Question

I have an excel file with 30 time cards, each on their own worksheet, where the only identifier is the worksheet name (ie the employee name). Each worksheet has a first column of account numbers, followed by columns for hours worked for each day of the month, and then total.

From these individual employee tabs I make a Totals worksheet(using =SUM('Adams:White'!B1) and then fill left and fill down. . .)

I then make a pivot on the Totals data and get summary data for the department. (ie we spent 100 hours total on account# 12345) - no problem.

My Question is: How do I write a formula(s) to find which employees contributed to the hours spent on account# 12345. The specific output I would want is a table with a column heading of "12345", and then only the names of those who worked on that account below the heading. (Or all names, sorted, with a second column of how many hours they worked on "12345").

Thanks! Steve

Answer 1

Since you are feeding your data set into a pivot table, you will need to ensure each record (row) in your data set is reportable. i.e. if Adam and Jane worked on account 12345 for a total of 7 hours and your record in your data set (table) is only one row with the account listed and the total number of hours, it will be difficult and extremely bad practice to attempt to report this by staffer (how do you know that the 7 hours is made up of Adam and Jane, or it could be 14 part-time workers that each put in half an hour).

You have two approaches. One: you could consolidate the data into a master data tab and from there you could have each sheet (Adam, Jane, White) be a report off the master table to show performance by staffer.

Two: Make use of power pivot, if you have Excel 2013+ installed. Here you would create a link for each table by account. Now you would have each rep's hours contributed as a field in the power pivot connection.

Please let me know which of the two seems a better choice and I can assist from there.