none
Excel Table with SharePoint Data Connection - Manual Text Entry Misaligned After Refresh RRS feed

  • Question

  • Greetings!

    I have an Excel 2010 workbook that includes a table linked to my SharePoint 2013 site by a data connection. The SharePoint list feeds the table standard information that's managed on the SharePoint site, but I need the user of the Excel workbook to be able to enter text manually in the workbook to associate local information with the line-items coming from the SharePoint list. To do this, I've added extra columns to the end of the table.

    The user can enter information in the appropriate cells in the "extra" columns at the end of the table, but when I refresh the data connection, the addition of a new list item on the SharePoint side results in the user's manually entered text getting out of alignment with the row it's supposed to be associated with.

    Example

    Column 1(SP) Column 2(Extra)
    Row 1 Item 1
    Row 2 Item 2 Text entered for Item 2
    Row 3 Item 3

    Then, if I add a new item to the list in SharePoint, for example, something that would appear between the original items 1 & 2, after refreshing the table, I get the following:

    Column 1(SP) Column 2(Extra)
    Row 1 Item 1
    Row 2 New Item 1.5 Text entered for Item 2
    Row 3 Item 2
    Row 4 Item 3

    The table's data connection is set to insert rows for new items, and I could swear I had this working properly once upon a time...but I can't seem to make it work now.

    Any thoughts on what would cause this?

    Thanks in advance!


    Saturday, August 9, 2014 3:05 AM

Answers

  • Hi Eric,

    >>but it seems that by extending the table itself to encompass both the SharePoint-sourced columns and the additional columns, that an association would be created between all columns for a given row in the table, no?<<

    From my understanding, the answer is no.

    Another example:

    I have an additional column named "Column2" and an external column named "ID" (see "before").

    After I add a new record and refresh this table, Excel will keep the last row in "column2" and add an empty cell in the second last row. (See "After")

    If I delete the eighth data and refresh this table, Excel will still keep the last row and remove the last second cell.

    In your case, if you insert a new record in the middle of the data and refresh this table, Excel will synchronized the external data and add a new cell in the additional column in the second last row.

    From my understanding, Excel will always change the second last row to suit for deleting or adding record.

    Hope this helps.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by eculver3 Tuesday, August 12, 2014 4:15 AM
    Tuesday, August 12, 2014 3:11 AM
    Moderator

All replies

  • Hi,

    >>Any thoughts on what would cause this?<<

    Since "Column 2" is an additional column and there is no relationship with any record in SharePoint.

    While refreshing the table from SharePoint, Excel will only re-create the table and Excel doesn't know which record is related to the additional data. This is by design.

    If you want to make them work with each other, you need to create such column from SharePoint. After that, the data of this new column is all related to every record.

    In addition, this forum is used to discuss questions for developers, that means most of threads are related to code. If you have any Excel question without code or related to Excel feature, feel free to post in Technet Excel IT Pro Discussions forum. Thanks for your understanding.

    Regards,

    George.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by eculver3 Monday, August 11, 2014 2:12 PM
    • Unmarked as answer by eculver3 Monday, August 11, 2014 2:16 PM
    Monday, August 11, 2014 8:34 AM
    Moderator
  • Hi George,

    Thanks for your answer. I realize that on its surface this seems like more of an Excel feature question, but I posted it here because I can foresee a code solution to this design issue, if that's truly what I've run into here.

    I realize that an "additional column" placed adjacent to the table would have no relationship with the records coming from SharePoint...but it seems that by extending the table itself to encompass both the SharePoint-sourced columns and the additional columns, that an association would be created between all columns for a given row in the table, no?

    If this isn't the case, then I can see that I'd need to write a little code to take care of this association behind-the-scenes for my users.

    Thanks!

    --Eric

    Monday, August 11, 2014 2:25 PM
  • Hi Eric,

    >>but it seems that by extending the table itself to encompass both the SharePoint-sourced columns and the additional columns, that an association would be created between all columns for a given row in the table, no?<<

    From my understanding, the answer is no.

    Another example:

    I have an additional column named "Column2" and an external column named "ID" (see "before").

    After I add a new record and refresh this table, Excel will keep the last row in "column2" and add an empty cell in the second last row. (See "After")

    If I delete the eighth data and refresh this table, Excel will still keep the last row and remove the last second cell.

    In your case, if you insert a new record in the middle of the data and refresh this table, Excel will synchronized the external data and add a new cell in the additional column in the second last row.

    From my understanding, Excel will always change the second last row to suit for deleting or adding record.

    Hope this helps.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by eculver3 Tuesday, August 12, 2014 4:15 AM
    Tuesday, August 12, 2014 3:11 AM
    Moderator
  • Thanks for the additional explanation!

    --Eric

    Tuesday, August 12, 2014 4:15 AM
  • Hi George,

    You claim this is by design. Can you help me to understand why anyone would want that?

    If "f" is associated with the 7, it should stay there and Column2 should be blank for ID 8. Then the user would enter the new information in Column2 that's associated with the 8. Otherwise, the user is forced to move the data back up to where it belongs every time.

    Thursday, January 29, 2015 11:10 PM
  • George,

    If you were to these "additional" fields to the SharePoint site, but the additional fields were still being updated manually in the workbook (file that you're sourcing data TO), then refreshing would clear out these new manual entries since the record(s) were not updated in SharePoint.

    Basically, you can update all or nothing.

    - Charlie


    • Edited by charlatain Wednesday, May 18, 2016 4:44 PM
    Wednesday, May 18, 2016 4:44 PM