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

TomE

New Member
Joined
Feb 21, 2002
Messages
28
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top