locked
Linking Two Excel Worksheets in a SharePoint 2010 Environment RRS feed

  • Question

  • We're intereseted in sharing a worksheet between two SharePoint 2010 document libraries. One of the libraries will be view-only for most users. We'd like to reflect any changes in one worksheet to show up in the other. Is it simply the easiest to copy and past-link between source and destination to accomplish this?
    Orange County District Attorney
    Friday, June 24, 2011 10:29 PM

Answers

  • Hi

    In that case use the formula I gave you first. It needs to be copied from A1 to all the other cells in the worksheet.  You should be able to do that by copying A1 then clicking the button above the first row number (1) then pasting.

    Any cell linking to an empty cell will appear empty.

    Hope this helps


    G North MMI
    • Marked as answer by Sandy Wood Wednesday, July 6, 2011 2:42 PM
    Tuesday, July 5, 2011 6:05 PM

All replies

  • You should be able to link to the excel document directly to the sharepoint server's UNC path.

    To find the UNC path to your excel file:

    1)  In your sharepoint site, browse to the document library containing your source excel document .
    2)  Select "Actions - Open with Windows Explorer"
         (This will launch windows explorer with the full UNC path to folder containing the source excel document)

    Now Open the destination excel document

    1)  Decide where you want the data from the source to be displayed.  I suspect it will be it's own worksheet in the destination workbook
    2)  While on the worksheet where you want the data displayed, go to the Insert Tab
    3)  Select "Object" under the "Text" category
    4)  In the Object Dialog Box, go to the "Create from File" tab
    5)  Click Browse
    6)  In the File name: field, enter the UNC path to the source excel spreadsheet and hit enter or click insert
    7)  Select your source spreadsheet from the displayed files, and click insert
    8)  Place a check in the "Link to file" checkbox and click OK

    And there you have it.  When the source file is updated it will be reflected in the destination file via the embedded linked object.

    • Proposed as answer by AmyLeeB Thursday, October 25, 2012 8:44 PM
    Sunday, June 26, 2011 12:46 AM
  • Is there a way to add the specific Worksheet reference to the link? Something like

     

     =Excel.Sheet.8


    Orange County District Attorney
    Monday, June 27, 2011 8:59 PM
  • Yes

    =[Workbook]Sheet1!$A$1

    This would reference Cell "A1" in Worksheet "Sheet1" in Workbook "Workbook".  However, to use a reference like this, the referenced workbook must be open in the same excel application instance for the values to be updated.

    For example.

    Workbook1
         A1 = "=[Workbook2]Sheet1!$A$1"

    Workbook2
         A1 = "TestValue1"

    This will cause Workbook1 to show "TestValue1" in Cell A1.

    If someone opens Workbook2 and changes A1 to "TestValue2" and then closes Workbook2, when they open Workbook1, a message will be displayed indicating that links could not be updated.  When the message is accepted, Workbook1 will still show "TestValue1" in Cell A1.

    If someone opens Workbook2 and changes A1 to "TestValue3" and then opens Workbook1 in the same excel instance, Workbook1 will update its links and the Value of "TestValue3" will be displayed in Cell A1 of Workbook1.

    Hope this helps.

    • Proposed as answer by AmyLeeB Thursday, October 25, 2012 8:43 PM
    Monday, June 27, 2011 10:21 PM
  • So for this to work, both workbooks need to be open? Workbook2 will be set for read-only so for our purposes no one will be making any changes.
    Orange County District Attorney
    Monday, June 27, 2011 10:37 PM
  • You can actually specify the full path to the workbook in the formula and it will update without you having the source workbook open, but I have found this method to be flaky at best.

    Structure:
         ='<Path to file>[<File Name>]<Worksheet>'!<Cell Reference>

    Example:
         ='\\server\share\folder\[Source.xlsx]Data'!$A$1

    In order for the links to update automatically, you have to add the location of the file that the users will open, (not the read-only data source), to the Excel Trust Center

    1) Click Office Logo [top left corner]
    2) Select "Excel Options"
    3) Select "Trust Center" in the Left Menu Pane
    4) Click the "Trust Center Settings" button in the right view
    5) Select "Trusted Locations" in the Left Menu Pane
    6) If necessary, check "Allow Trusted Locations on my network".  If your viewing spreadsheet is in sharepoint, you will need to enable this.
    7) Click "Add new locations"
    8) Enter the full UNC path to the location of your viewing spreadsheet
    9) Click OK, OK, OK

    The experience I get with this method that makes me say it's flaky, is when I open the viewing spreadsheet, I get a message saying "This workbook contains one or more links that cannot be updated."  If I "X" the message and close excel and then immediately open the viewing spreadsheet again, it opens fine with the updated data displayed from the data source spreadsheet.  I can then open and close the viewing spreadsheet as many times as I was in succession and it will open and display the data with no problems.  If I then close the viewing spreadsheet and do not open it for 1 minute and then open it again, I get the "This workbook contains one or more links that cannot be updated." dialog again.  The only way I know to get the correct data to display consistently is to have the data source spreadsheet open at the same time as the viewing spreadsheet.

    It seem to be a timeout or sequence issue to me, but I don't know of any way to tweak excel to resolve this particular flakiness.

    • Proposed as answer by AmyLeeB Thursday, October 25, 2012 8:38 PM
    Tuesday, June 28, 2011 12:08 AM
  • Thanks for the great information. I did follow your steps above and low and behold got the same "This workbook contains one or more links that cannot be updated." Dang.

    Since we in a SharePoint 2010 world, we're going to try and approach this from a document library side. I'm thinking maybe a SharePoint Workflow in the library that would save and update a copy of the specific worksheet everytime the master workbook was saved. Not sure if it will work but we'll try and see.


    Orange County District Attorney
    Tuesday, June 28, 2011 3:33 PM
  • Hi Sandy,
     
    I am writing to see how everything is going with this issue. Is the problem resolved? If there is anything I can do for you, please feel free to let me know.

    Sincerely,

    Max Meng
    Forum Support


    Come back and mark the replies as answers if they help and unmark them if they provide no help.
    Friday, July 1, 2011 6:36 AM
  • This may be a little late, but I have tried to recreate your issue using SP2010 and Excel 2010.  I have two files in seperate libraries with a link formula from file 1 to file 2.

    When I open File 1, I get the prompt to udpate links, but clicking Update provides the new value from the edited File 2.

    The only thing I can see that might be different from the information provided in this thread is the constuction of the formula.

    I have

    = 'http://site/subsite/library/[filename.xlsx]Sheet1'!$A$1

    as my formula.

    In fact I have two formula one to File 2 and another File 3 in the same library as File 1.  Both Update to reflect the current values saved in their linked files.

    So far I have open and closed the file numerous time and not see the links cannot be updated.


    G North MMI
    • Proposed as answer by AmyLeeB Thursday, October 25, 2012 8:43 PM
    Friday, July 1, 2011 12:30 PM
  • Good find G North.  I tested again with Excel 2007 and the issue I experienced is not present when utilizing the URL rather than the UNC to the file.  As a matter of fact, I tested by placing the formula in A1 to point to the external worksheet data using URL reference, and in A2 to the same external worksheet data using UNC path.  Although both pointed to the same spreadsheet and cell, only the one referenced using URL updated correctly.
    Friday, July 1, 2011 2:23 PM
  • Thanks for the comment on my issue. In your example, what if you wanted to show the entire worksheet, not just one cell?
    Orange County District Attorney
    Friday, July 1, 2011 3:19 PM
  • Hi

    If you really wanted to do this for a sheet then in A1, on a new sheet enter

    =IF( 'http://site/subsite/library/[filename.xlsx]Sheet1'!A1="","", 'http://site/subsite/library/[filename.xlsx]Sheet1'!A1)

    and copy through all the cells (or as many as you need to)

    You'd loose all the formatting but have all the data.  You may even obtain some error messages if the file to which your linking has merged cells.  You'd probably notice lags on recalculation, and updating links etc.

     

    I'm not sure you want to do this but there's little explaination on what you're trying to achieve other than linking files

     


    G North MMI

     

     



    Friday, July 1, 2011 3:31 PM
  • Thanks again for the contribution. I apologize for the dearth of explanation.

    What we're trying to do is share only one worksheet of a workbook in one document library on SharePoint. We've created another document library which will store the destination, read-only, workbook. It's in this spreadsheet that we want to display the linked worksheet for a group of users.


    Orange County District Attorney
    Friday, July 1, 2011 3:45 PM
  • In addition to the previous method.

    Use the copy sheet command to copy the sheet between the two files, save and close  Excel will write all the necessary formulae for you.

    As it's not clear what changes in the file your trying to include.

    Method one

    Will update with every possible change made to the worksheet, but loose formatting.

    Method two

    Will maintain the initial formatting and update changes where existing cells are overwritten, but will not include any data added to the worksheet, ie. a new row added at the bottom of a list. (formulae will only be created where cells have data when the copy is made)

    I'll leave you to decide which method might be best in your situation.


    G North MMI
    Friday, July 1, 2011 4:07 PM
  • Hi Sandy,

    I am writing to see how everything is going with this issue. Is the problem resolved? If there is anything I can do for you, please feel free to let me know.


    Sincerely,

    Max Meng
    Forum Support


    Come back and mark the replies as answers if they help and unmark them if they provide no help.
    Monday, July 4, 2011 9:05 AM
  • I'm sorry I wasn't clear in my needs. We want to create a copy of a single worksheet that will be updated with every possible change made to the source worksheet (except formatting). I hope this makes my needs clear.

     

    Thanks again!


    Orange County District Attorney
    Tuesday, July 5, 2011 3:46 PM
  • Hi

    In that case use the formula I gave you first. It needs to be copied from A1 to all the other cells in the worksheet.  You should be able to do that by copying A1 then clicking the button above the first row number (1) then pasting.

    Any cell linking to an empty cell will appear empty.

    Hope this helps


    G North MMI
    • Marked as answer by Sandy Wood Wednesday, July 6, 2011 2:42 PM
    Tuesday, July 5, 2011 6:05 PM
  • OK, thanks! I'll give it a try.

     


    Orange County District Attorney
    Tuesday, July 5, 2011 6:28 PM
  • Hello,

    I tried your solution and it seems to work. I'm seeing the data (less the formatting) from the source worksheet. I'm wondering what the mechanism is that gets the latest updates from the source worksheet. We've tested some changes to the source worksheet and then opened the destination worksheet. It took a few minutes for the changes to show. Do I need to configure a refresh of sorts in the destination worksheet?


    Orange County District Attorney
    Tuesday, July 5, 2011 9:44 PM
  • Hi Sandy,

    Please remember to click “Mark As Answer” on the post that helps you, this would benefit for the other community when they read this thread.


    Sincerely,

    Max Meng
    Forum Support


    Come back and mark the replies as answers if they help and unmark them if they provide no help.
    Wednesday, July 6, 2011 8:33 AM
  • Sorry.
    Orange County District Attorney
    Wednesday, July 6, 2011 2:42 PM
  • Thank you for your understanding and cooperation!

    Sincerely,

    Max Meng
    Forum Support


    Come back and mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, July 7, 2011 3:23 AM
  • Is it possible to do this with PASSWORD PROTECTED excel files on SharePoint without having to enter in the password of the source file (File 2) for every row of data I want to bring over/update from that file into File 1? 

    Both of my files are password protect but I want File 1 to update automatically when I edit File 2, preferably without me having to type in the password of File 2 over and over and over again for each row of data.

    Thanks,

    Amy

    Thursday, October 25, 2012 8:36 PM
  • Hello G North,

    I have pretty much same situation and need to create links between two different excel sheets. However I've found out that this formula doesn't apply on excel sheets with different permission levels. For example I have let's say master excel sheet (from which the data are fetched) - only certain people can see it and can modify it. Then I have the second work sheet which should display certain fields and different people have access to it - problem is that even though this formula is present I always receive "This workbook contains one or more links that cannot be updated."

    Do you know any workaround for this? I've been through the whole thread and it seems that problem is the same regardless which formula is applied.

    BR

    Aleš Krajina


    -- Ales -- DB admin

    Monday, November 26, 2012 7:54 AM
  • Hi

    Because this solution is formula based, there is no way to incorporate any bypass to access password protected files automatically.  Similarly user permission to files in SharePoint cannot be handled.


    G North MCT

    Tuesday, November 27, 2012 9:53 AM
  • i tried the below formula and it worked
    ='http://siteurl/Sheets/[Source.xlsx]Sheet1'!$G$4 + 'http://siteurl/Sheets/[Source.xlsx]Sheet1'!$G$10

    but the formula is not working unless the to 2 cells are consecutive, any idea

    WORKS

    =SUM('http://siteurl/Sheets/[Source.xlsx]Sheet1'!$G$5 : 'http://siteurl/Sheets/[Source.xlsx]Sheet1'!$G$6)

    DOES NOT WORK

    =SUM('http://siteurl/Sheets/[Source.xlsx]Sheet1'!$G$5 : 'http://siteurl/Sheets/[Source.xlsx]Sheet1'!$G$7)

    please note that is no issue with cell or its value

    Saturday, January 19, 2019 11:22 AM