Building reports in Excel, for data being entered in MultipleWorkbooks by Multiple users

  • Thread starter Thread starter Mike C
  • Start date Start date
M

Mike C

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.
 
Mike,

If the workbooks are all similar, you can use a macro to combine all the data into one file that
would then become the basis of your report generation. You can open all the files in one folder, or
one folder and all its subfolders, or just files with specific names, or files that you select, and
combine them into a single file, with other information as needed. The best macro would depend on
your specific needs, so describe a little more...

HTH,
Bernie
MS Excel MVP
 
Mike,

If the workbooks are all similar, you can use a macro to combine all the data into one file that
would then become the basis of your report generation.  You can open allthe files in one folder, or
one folder and all its subfolders, or just files with specific names, or files that you select, and
combine them into a single file, with other information as needed.  The best macro would depend on
your specific needs, so describe a little more...

HTH,
Bernie
MS Excel MVP












- Show quoted text -
Click to expand...

THanks for the response Bernie. The merge macro that you describe
would be a great start.

I need to open and combine five files that I can access from a
particular folder each week. Each workbook has the same tabs (one of
which needs to be merged for all) and the sames fields.

The title of the files will be AB.xls, KS.xls, SM.xls, DL.xls, and
RW.xls.

I need the contents from one of the tabs in each sheet (titled
"patient list") to be combined into a single tab. So if there are 20
records in each workbook, I would need 100 records in that tab in my
combined workbook. That combined workbook can be my workbook for
querying and pivoting the data....

Thanks for any suggestions.

Then I can
 
Back
Top