0

I'm working on a project for a client. This client uses a form in SharePoint to enter and save data from certain projects into SharePoint lists. This data is setup to connect to an Excel file, that is working as a report on the data (pivottables, graphs etc).

The connection between the spreadsheet and SharePoint is established. The problem arises when my client enter new columns in SharePoint (to hold new data) to existing (and future) projects. When the Excel file is opened and tables are refreshed to fill in the new data, an error pops up. The error message is in danish, but translated it sounds like this:

The action is not allowed. The action tries to move cells in a table in the spreadsheet.

I reckon this has something to do with the current table doesn't fit the data-structure from SharePoint?

What can I do about this? Does the connection with Excel has to be rebuilt? Or can an automatic solution be set up, so that the Excel table always fits the SharePoint data, even when additional columns with data are added in SharePoint?

I have no experience or expertise with SharePoint, but I'm fairly good at Excel. Unfortunately I'm not able to upload any file or reveal any SharePoint data, since this is very confidential.

Any help on this subject will be greatly appreciated

Improve this question

1 Answer 1

Reset to default
0

I take it the Excel file has a data connection to a specific view. Does the Excel table have any calculated columns appended to the imported table? If new SharePoint columns are added to the view used by the connection, this could be the reason for the error.

If you do NOT have any Excel calculations in the table:

In SharePoint, try to place any new columns of the list after already existing columns in the view that is used for the export. This way, the Excel table will simply need to expand to the right and no existing data will be touched.

Inserting new columns between two existing columns may cause problems.

If you DO have Excel calculations in the table, you may need to temporarily remove the calculated columns, then refresh the data connection to accommodate new columns and then put the calculated table columns back into the table.

Improve this answer
5
  • It seems like you are correct :) I do have calculated fields in the furthest right side of the table and it seems like they caused the problem. If I remove those calculated fields, and insert them after I update, can I then make a table that is wider than the sharepoint data (but with no calculated fields) just so I don't have to edit the table size everytime I update with new SharePoint fields? Thanks in advance. I really appreciate your help and I apologize for the slow reply.
    – Boerge
    Sep 22, 2015 at 14:36
  • I've tested some more. It seems like its the tables that messes it up. My client has seperate lists for keeping data in different areas. So the connections from SharePoint are set up to several tables. Lets say they have 4 Business units that enters data seperately. Then the information is stored in 4 different SharePoint lists and then connected to 4 different tables in Excel. Theres a consolidated pivot table set up from the data from the 4 different tables. When I set it up once with the current number of columns from the SharePoint data it works fine when I update.
    – Boerge
    Sep 22, 2015 at 15:33
  • But when I add new columns to the SharePoint data (in all the lists) - the warning I posted before comes up. Even when I have removed all the calculated fields from all the tables and the tables have the same number of columns. FYI: all the tables are placed in the same sheet below each other. You can see here the structure behind how its set up: imgur.com/CYg5Hom
    – Boerge
    Sep 22, 2015 at 15:41
  • The problem is that the tables are underneath each other. If one table grows, it will need to move or overwrite the table below it. That is not allowed. If you consolidate the tables into a pivot table, create them side by side instead of underneath each other. Or let each table use a different sheet.
    – teylyn
    Sep 22, 2015 at 20:52
  • You were right. Thank you very much for your replies. I've fixed the problem now, with your help :)
    – Boerge
    Sep 28, 2015 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.