Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post

    Pivotable and other linked data to external sheets

    I have a master document that grabs data for it's pivot tables from one external file and grabs data from another file directly. When I load up the master document everything it's referencing from external files shows #REF. If I load up the documents it works fine.


    How do I make this work without having to load the other documents?


    Once that works... How do I make it so people who open the document and don't have access to the external documents see what was last saved?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    How does the master document get the data from the other file (which formulas)?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post
    Hi Rory,
    Here's the first part of the formula with the filename
    =GETPIVOTDATA("Period 13 Actuals ",'C:\[2-Key Strategic Indicators QB Source Data.xlsx]P&L Pivot'!$A$1,

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    Looking at the formula, you can only get it this way if the source file is also opened.

    The best way to deal with your problem, particularly if your want other people to open your document (without having access to the source file), is to import all of the source data from the eaxternal master file into your file, and amend your formulas to now look at the imported data sheet.

    zeddy

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post
    I don't care about other people having it, although I want them to be able to see the last data I updated with. I would like to not have to open the other file though. Is there a way?

    Thanks,

    Bryan

Posting Permissions

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