Automatic Excel list exported from SharePoint not keeping my local data?

Question

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?

Answer 1

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

Complete the pop-up box the same as this:

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.