Collating data from multiple sheets

Hi all,
I'm very much learning how to use Excel at the moment. I work in a hospital and help to run a service that reviews patients and produces care plans to facilitate earlier discharge etc.

Background: Our current excel workbook has individual worksheets for each employee in the service to record the data specific to the patients that are referred to them. I have created a simplified example (minus real patient information of course!) so that you all can see what I mean.

The manager of the service requires various statistics to be collated. My thought so far is to have a separate worksheet within the workbook for the manager's stats but I don't know if this will work well or not.

What I need:
1. Collated stats from Columns E, F and I from EACH of the worker’s worksheets in the Statistics worksheet.
Could a pivot table do this? That is, could I use multiple worksheets as source for the pivot table? If not a pivot table what do I need to do in order to collate these stats?
2. I also need to collate the total number of new referrals to our service (each month/year/etc.)

Restrictions:
This is a SHARED workbook so that means I cannot use tables (unless someone has a workaround for this).

Sorry this is where my novice understanding comes in: I'm not sure what you mean by a single slicer. What I was wondering is if I can take data from multiple worksheets and have one pivot table that shows all that data.

Thanks for the reply. I've attempted this but I think the fact that it is a shared workbook means I can't create a pivot. So I guess pivots are out, is there any other way to collate the data I need? (see start of thread)

Originally Posted by humdingaling

you can use pivot but you cant amend..which kind of defeats the purpose

Agreed! There's not much point to a pivot that you can't manipulate.

Originally Posted by humdingaling

anyway my proposal was to use a macro to do the grunt work of taking data from each worksheet
see attached sheet
click the button to make it run

I tried this but maybe I'm doing something wrong - There was no button to push, just a "drawing" of a box that says "Combine". If I click on the box it just highlights the Drawing Tools function on the ribbon. I know next to nothing about macros but I get the feeling that I need to start learning quickly!

In the meantime I have found a work around (of sorts) but simply getting the averages from the individual sheets and then adding them, its just a two-step process instead of a one step that's all.

Cool thanks. I can see that it works, but because I know nothing about macros I can't seehow it works. So, until I get the time to train myself up on macros I'll stick to formulas. Thanks for the help.