Hello - I have a group of nurses that keep a lot of patient data in
standard excel workbooks.  I need to pull reports based on that data
on a weekly, monthly, and sometimes daily basis.

For now, I have been having them send me the data, and then I copy it
all into a single workbook, and then I build the reports.  But I would
like to avoid this step, if possible, and somehow automate the
selection of the data in each of their workbooks.

Does anyone have any experience doing something like this.

Is it even possible to perform advanced filters and pivots (i.e.,
reports) of multiple workbooks from a central workbook?  I would even
want to do this while they have their workbooks open....

I imagine that I it would be best for me to use Access to do this, but
I don't have the time.

If someone has done something similar to this in Excel, I would be
interested to hear how it went and any tips you may be able to
provide.

Thanks for any thoughts.