Dynamically Link a Summary Master List to Multiple Worbooks/sheets

**SeanPratt** · 10-25-2012, 03:58 PM

Hi guys,

What we have are multiple workbooks (one per employee) to track projects. Each one is set up identically and I need to have a dynamic link(s) to each worksheet in each workbook.

The range of cells are A2:F# (The amount of rows is variable). Each worksheet represents a month and year (Aug 2012, Sep 2012, etc.) . I need the summary data to link to the corresponding tab in the master sheet. If an employee updates (adds/deletes/edits) his or her workbook/sheet it should cascade into the master sheet (perhaps after running a macro?).

The employee project workbooks are all in a single directory. The master workbook is one level deeper in an Admin folder.

I have toyed around with a bunch of modules and functions but have not been able to achieve any success.

Any ideas?

**arlu1201** · 10-26-2012, 01:09 PM

You can have a macro that summarizes all the employee workbooks into your master workbook. If there are changes made to the employee workbooks, its better to run the macro again to re-consolidate the data. However, it will have to clear out the summarized data first and then re-populate to avoid duplicates.

My question is - Will there already be data in the Master workbook, like last week's data or last month's data? Or is it a fresh workbook that is used as a master workbook each time?

**SeanPratt** · 10-26-2012, 01:22 PM

The Master file will contain existing data (previous weeks/month's data). It would be best if the slate were wiped clean and the data recompiled (say a button macro).

Also, there are worksheets like Aug 2012, Sep 2012 in each workbook and the Master will have correlated worksheets. Can the compiled data be targeted by the worksheet title?

I appreciate your help on this.