none
Excel file connected to an external SharePoint list

    Question

  • Hi,

    I have an Excel file which has a connection to an external SharePoint list.
    This results in a defined table in this specific Excel worksheet.
    This table is synchronized with the SharePoint list in one-way each time it is opened.
    The data concerns basic HR data from personnel.

    In this Excel worksheet, I defined another table aside of this existing table.
    Several formulas reference columns in the first table.
    So far, everything works fine.
    It's very important that row X in the first table corresponds to row X in the second table.

    In SharePoint, an item is added (new personnel).
    Excel is opened, the connection is established, the table is updated.
    But the references in the second table are NOT updated. This results in a mismatch
    between the rows in the two tables.

    I've did several test with simple excel tables in one worksheet, and it works fine.
    Adding a new row in table one will adapt the references in the second table.
    But it doesn't work when this first table is established through an external connection.

    Thanks for any help!

    Thursday, October 26, 2017 5:18 AM

All replies

  • Please provide your SP version and Excel version.

    We will try to test this problem.

    Friday, October 27, 2017 10:07 AM
  • I have a table in Excel 2010 with connection to a SharePoint 2010 server.
    The first column is the name column, which contains unique values.

    Aside of that table, I explicitly define a second table in the same worksheet with the first column also called name. These values should be identical to the values from the name column of the first table.

    The idea is that all columns in table 1 contain HR data about person X. This data comes from the SharePoint list. In table 2, all additional columns aside the first column contain specific metadata about this personnel.

    If a new person arrives, it's added in the SharePoint list, so it appears in the first table in Excel (exposing the basic HR data). But in the second table, a new entry should be added with the first columns containing the name and of course all other columns being blank.

    The idea should also  work the way around. When somebody leaves the organization, he/she is removed from the SharePoint list. Automatically, this person is removed from the table in Excel (after synchro). But it should also be removed in the second table aside of the first one.

    Working with relationships and an intermediate table didn't solve my problem.

    Thanks


    Saturday, November 11, 2017 7:32 AM