PDA

View Full Version : Multiple Spreadsheet data combined into one, and then again output into one workbook



CLAASSJA2421
11-02-2016, 10:58 PM
Good day,

Back Ground (for clarity)
I work in a deportment where we have a shared drive. We have 24 clinics who have to submit a weekly out of stock report (the reports are pre-populated on the shared drive, 53 excel files per clinic divided into the months of the financial year and then from week1-5). The out of stock reports need to be authorized by Area Pharmacist and only the items that are confirmed as out of stock will pull through to the needed reports. I have done a lot of work on this but I am looking for an easier way of doing it.

Current workflow: (i am attaching all the relevant files)
Master List.xls: This is a list of authorized items kept by all the clinics. This list may change monthly and all the spreadsheets refer to this list. Next to the items they are catogorised into EDL, Surgical, CoT, Tracer and Rx items. These catagories are used for different reports
Week#.xls: The files are listed per month as Week1.xls, Week2.xls... depending on the number of Mondays in a month. The report is done by selecting the item name from a drop down list (Master List). and then saved. The Area Pharmacist the Authorize by putting a "1" or "0" in the RP column.
SDBIP All.xlsm: This file contains vlookup formulas that refer to all the clinic reports for each month, also using the Master List. i have designed that this file needs to be opened, saved and closed. It divides the items into different columns as they are categorized in the Master List. Each clinic then has a sheet in this workbook that summarise the items reported per week per category.
SDBIP.xlsm: This is the file used for reporting. It is also divided into months and refer to the SDBIP All.xlsm Spreadsheet. The data needed is selected from the dropdown menus on top and the report required (EDL, Surgical, Vaccines.. etc.)

I need to get a report from all these spreadsheets that has the same output as seen in the 17 October Narative Report. If there as a VBA that could be used to get the information from all the different weeks, and then combine it into a singular workbook that would make my life so much simpler.

Any help would be greatly appreciated

PS:"if there are any password protection still on the password is "Gismo" "
17494
17495
17496
17497
17498
17499
17500
17501