HELP Creating a summary sheet from multiple worksheets which totals with two variables (which are existing data tables)

Question

Hi,

I am trying to create a summary top sheet for monthly worker timesheets. I want to create a summary per month (each worksheet=calendar month) which will either display the data in subtotals per two fields- Employment Type and Pay Type- or can be filtered by each of these fields (one of which currently exist in the sheets as drop-down from a data table- Employment Type)

I can easily pull the totals by Pay Type out onto the top sheet- but can't figure out how to add in the Employment Type field as either a linked variable or filter options. Screenshot of one of the monthly worksheets below. Highlighted in yellow the totals I am looking to pull out of each sheet. Column D= Employment Type Column N= Pay Type (which is at the moment just text)

I don't have in-depth knowledge, so I have tried to figure out with a Pivot Table, using consolidate and sumifs but I don't have enough knowledge to make these work. If someone can describe in plain terms I would be ever so grateful!!

TIA

Answer 1

Great!!!!!

I'm glad you found a solution in my reply

Thanks for the positive feedback

As for your question, you may refer to the videos below for more info.

Regards

Jeovany

https://www.youtube.com/watch?v=GUClkvJ7Gag

https://www.youtube.com/watch?v=QWizkfED000

https://www.youtube.com/watch?v=jPajIyavh_0

Answer 2

Hi

You may find in the link below a copy of your file with the answer to your question

https://we.tl/t-ANueXXLV3J

Regards

Jeovany

Answer 3

The days of January 2021 include portions of six weeks.

Should you not add a "Week 6" column?

Answer 4

Hi,

Thanks for your reply, you're quite right but it was just test data in there- and our staff only work in term-time to that means only 4 weeks in January :-)

E

Answer 5

Hi again Jeovany,

Thanks again for all your help- I have returned to my original sheet and managed to set it up in my original format and with data. One remaining question; it's working great and I understand the use of INDIRECT function for my summary front sheet, it's working fine. This relies on the SUMIFs total tables on each monthly sheet which I now have all my data in and are also working. But my question is about what happens when we need to add further rows of employee data in the future. At the moment, the total tables live in the same place each monthly tab, meaning an INDIRECT reference to the common cell number for each total works across the board. If I add a new row in to add some more data in a particular month, it of course sends the totals table down the page and my summary no longer finds the information through the indirect cell reference. Can I lock this so I don't then have to change the cell references to get the summary?

Many thanks!!