Produce summary workbook sheet with data from multiple workbooks

**Seancsn** · 03-26-2013, 04:21 PM

I have created a template workbook for individuals who work on different sites. The spreadsheets used by each individual have the same headers. The sheets all sit in the same folder in dropbox.

I am wanting to produce a summary workbook within the dropbox folder that automatically populates when an individual updates their workbook.

I have attached mock example of the individuals worksheet for your information.

The questions that I have are as follows:

1. Is it better within an individuals workbook that they record their activity all on one sheet as opposed to having a spreadsheet per months activity? It seems tidier to have a sheet per month but does this make it more difficult to produce a summary workbook?

2. The individual is required to record a patients individual drugs on separate lines but they need to record a unique identifier, age, *** and month worked. Do they need to record all this for each line of drug or is there an easier way?

3. How should the summary sheet be produced - pivot table or sheet with formulas linked to the individuals workbooks?

Thanks in advance

Sean

**arlu1201** · 03-27-2013, 06:03 AM

Here are my views -

1. Is it better within an individuals workbook that they record their activity all on one sheet as opposed to having a spreadsheet per months activity? It seems tidier to have a sheet per month but does this make it more difficult to produce a summary workbook?

If you are going to use a macro to create your summary file, then having 1 sheet per person or 1 sheet per month does not matter. It may only create a problem for you to maintain. But if you are fine with having one sheet per month, you can continue to do so.

2. The individual is required to record a patients individual drugs on separate lines but they need to record a unique identifier, age, *** and month worked. Do they need to record all this for each line of drug or is there an easier way?

If there is a unique identifier / id per drug then you can have a lookup table stored somewhere in your file (as a separate tab maybe) and then as soon as you enter the id in your sheet, the rest of the details will be looked-up from your table. Or if the above record has the same info which needs to be copied to the row below, you can have a macro set up that completes all the rows for you at one go.

3. How should the summary sheet be produced - pivot table or sheet with formulas linked to the individuals workbooks?

You can try with pivot table. But do not have it as formulae linked to the individual workbooks. Its only going to make it more messy and there are chances of corrupting your files. Instead, you can have a macro that pulls the required information for you.