2

hope you can help!?

I have set up a table in Excel which does a great job of exporting data from a SharePoint however I have added my own columns to add (local) information to each entry.

ie. I am exporting a list of products with various columns showing detail (such as price, quantity, description, etc.) which is always updating on the sharepoint so I've connected my excel sheet to automatically update - and this is all done which works great.

BUT I have added a few of my own comments as we need to demonstrate how we have used each product (ie. classify how we have used the product, local product manager and comments). This is detail which we only want to keep local and something the central sharepoint can not add - however each time i refresh this excel table, the information I have entered (and saved) it disappears as the new data is transferred in.

SO - what i need to do, is keep the table refreshing with the new data from the SharePoint but also keep my added information on the columns that I have added.

Please help?

Improve this question
1

Select your data table and then on the ribbon go to Data>Connections>Properties

Complete the pop-up box the same as this:

Data Connection Pop Up

That should be all you need to do. If not set up like this Excel replaces the entire table whenever you update the connection.

EDIT
Not sure if this is the answer, but I had a bit of a think about your problem. I've encountered issues with appended columns being destroyed, and this was what I fixed with my answer above, but if you have set data moving around...

When Excel updates, I don't believe it keeps a relationship between the data in your table, and the data from SharePoint, it just replaces all the data with the data from SharePoint, which won't always be in the same order. I just had formulas in mine, if you're adding comments and then new data is being added then excel won't always refresh the data with the rows in the same places, and your comments will move around.

If you are using a default view for your export create a special export view. Ensure it is sorted by ID only. This way you can ensure that the location of a given row will always be in the same place, as new rows will always be added beneath. You can just sort the table in a more logical way within Excel.

Improve this answer
  • Thanks but I've already got it selected like this and the entries still don't stay... In fact they seem to jump into different rows? – James Bunt Sep 19 '13 at 15:30
  • This data is stored in extra rows that you've appended to the end of the table, right? – CLockeWork Sep 19 '13 at 15:39
  • (sorry about the delay - been a bit ill!), yes I've added a new column in the excel table (it's actually in the middle of the table opposed to the end but I don't think that is an issue) but as the table is updated and the new columns are added (with the new info from the sharepoint) the cells that I have completed locally in the new column stay on the same row and don't follow the older data down? – James Bunt Oct 4 '13 at 14:41
  • AH - I just read your original answer again... I have got the sharepoint data to auto filter by date so the newest rows of data come on the top of the graph... this could be causing the issue? I could (not that I really want to though as it makes things harder) auto filter from old to new however the new issue with this though is that an existing entry could get updated and then as the modified date has changed, it would go to the bottom of the list and loose the local data that I added to it? – James Bunt Oct 4 '13 at 14:48
  • Sort on the SP list view that you're connecting to by ID. That way the data will always be in the same place in the list, no matter what you do. Then just sort the Excel table by date, depending on the version of Excel (works in 2010) the table should reapply the sort when you refresh it. – CLockeWork Oct 7 '13 at 8:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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