How to pull data from seperate workbooks to update "parent"
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

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

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    ~Anne Troy

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Boston
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com