compiling multple worksheets

[Ian Truslove]

Hi
I'm a Supplies Manager in a Hospital. Each day the clinical departments
supply me with a excel spreadsheet detailing their requirments for
consumables for the next day. I've created these spreadsheets using a
"lookup" table which fills in description,specification number and loction
within the stores if they type the code number of the product required. I've
never used "lookup" before and have been feeling really smug as to my
cleverness !!!!!
BUT I NOW NEED SOME HELP FOR THE NEXT STAGE.

Problem
Each of the sheets ( approximately 15 ) may contain the same products, this
means I might potentially go to the same location point within the stores
multiply times to collect the same product.
Question
Is it possible to load multiple worksheets ( all in exactly the same
format ) into a single worksheet to enable me to sort into stores location
and total required for each product?

With my limited knowledge I would cut and paste each of the sheets into one
master sheet and then sort by location code, I would then total each of the
quantitys required by product code. Unfortunately I suspect my super time
saving idea would take longer than the old way.

I would appreciate any thoughts or suggestions

Thanks
Ian Truslove

 

[jaf]

Hi Ian,
Yes, it is possible.
However I would suggest approaching the problem another way.
Assuming your all on a network, I would create one workbook.
Assign one sheet within the workbook each department and create one
consolidation sheet.
Have them open the workbook and update their sheet.
You'll have to enable sharing for the workbook but now everything is in one
place.

The next part is a bit much for worksheet functions, but a simple macro with
automate every thing.
The macro needs to cycle through each of the department sheets, copy &
paste the data to the consolidation sheet. Then delete the data from the
department sheets.

Now that all the data is in one table, all you should need to do is sort the
data by location code, and you have a pick list.

And if each department has a ID code associated with each item, you don't
need separate sheets. One will do.

 

[Andy Brown]

OK, this is a pain (I said I hated them). I can build a table with multiple
consolidation ranges (using dynamic names) fine ; but it seems the source
books need to be open to refresh, else I get eg: "Reference is not valid ...
cannot open pivot table source file 'Test1.xls'. It wouldn't be a huge deal
to open all source books from one folder with shift-click, refresh, then
shift-Close All, but can anyone say if this is normal behaviour?

TIA,
Andy