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

Hi Eilidh

It is difficult to visualize your requirement with the current information, your workbook is too complex to give you a solution without having access to your file.

Kindly suggest prepare and upload a sample file to Onedrive, Dropbox, etc ... and share the link here

1) Zip the file before upload it

2) Remove any confidential/sensitive data.

a) In this case, Replace employee names with Employee-1, Employee-2,  Employee-3, etc... 

b) Important: Keep the headers as they are, as well as the table layout.

It will help us to give you a prompt and right solution.

If you need help with how to do it please, click the link below

https://support.office.com/en-us/article/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

You may also try

https://www.youtube.com/watch?v=NnXsE0SNuCc&t=14s

Regards

Jeovany

Answer 4

Hi Jeovany,

Thanks for your advice and please find here the link to an example worksheet of the problem I have. I hope someone enjoys the challenge of responding!

https://www.dropbox.com/s/nhsu03byf3zx9ho/Eilidh%20excel%20summary%20q.zip?dl=0  

Many thanks,

Eilidh

Answer 5

Thanks for sharing the file,

I'll work on it.

I'll come back to you If any questions as well.