How to pull data from seperate workbooks to update "parent"

[TomE]

Ok, here's the skinny:

We have a project status excel workbook that everyone updates via a shared drive. Very inefficient, as you can imagine.

Being the case, I would like the individuals to update their "own" workbook and have this data populate the "parent" workbook (including the addition of rows, not columns).
i.e. The parent workbook contains no data except the header cells. The data could then be filtered using auto filter.

Example:

Parent workbook has header columns:

Project Owner Status

All other workbooks have same header configuration.

Buffy enters her status information in her workbook; Otto enters his information in his workbook.

Our manager Biff, opens up the parent workbook and sees the data we entered and is able to filter this data via auto filter of the header columns (e.g. He wants to just see my projects).

Suggestions?

Great forum, by the way

[Anne Troy]

This will work much like your workbook does now. However, just copy the existing sheets to new workbooks, put the workbooks where they'll permanently reside. Then, copy all the cells from one person's workbook. Go back to your *original* and go to the right person's sheet and hit Edit-Paste special-Paste links. Do the same for all the people's workbooks. It should work quite similarly.

[owens_pastor]

Get files macro I adopted from mrexcel :


sub getfiles()
Application.ScreenUpdating = False
Sheets("Menu").Select
PathName = Range("D3").Value
Filename = Range("D4").Value
tabname = Range("D5").Value
controlfile = ActiveWorkbook.Name
Workbooks.Open Filename:=PathName & Filename, updatelinks:=0
Sheets(tabname).Select
Range("a1:j42").Select
Selection.Copy
Windows(controlfile).Activate
Sheets("P&L").Select
Range("a1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("a1").Select
Windows(Filename).Activate
ActiveWorkbook.Close SaveChanges:=False
Windows(controlfile).Activate

so you set up tab called "menu" in a sheet for Biff containing the path, file and tab name of buffy's and otto (cell D3, D4, D5 respectively). Biff have to run above macro to get an update from buffy's and otto's spreadsheet

[TomE]

Thanks to you both. I have tried the first suggestion above and while it works, it does not address the issue of adding additional rows to the child workbook being reflected in the parent book. In essence, when this is done, it is a cell to cell link.

To avoid having to share the parent workbook, I would prefer the data was being drawn from a different workbook as opposed to sheets within the same workbook (this is an option I have been playing with).

The macro I will have to play with, although I believe I'll be dealing with the same row insertion problem.

I build web apllcations and this does lend itself to asp and sql nicely (even access would work as a data source for the Excel file); I prefer to avoid this if I could.

Thanks and if there are any more suggestions, I'd love to here them.

[Jack in the UK]

Hi

As much as i avoid them can you not pivot table and refresh it to master sheet, devide to each as filtered and pull back... sort as reqd.

Just a thought

Rdgs==========
Jack