Excel keeping data integrity with external data

Question

I have a spreadsheet that is drawing data in from another spreadsheet via a connection. I'm wanting to add other data to each entry drawn from the external source and have it saved only in the current spreadsheet. The problem is, when refreshing the list (this list can and will change values and order), the corresponding data added to the local spreadsheet changes location.

From what I understand, excel is deleting the rows and reading them with the new data.

The solution would be to map the additional data to the unique ID for each entry, which is present in the data drawn from the connection, I just have no idea how to approach this in Excel.

Example sheet, where columns A and B are drawn from the connection, and columns C and D would be manually entered into the sheet.

In an ideal world, the values would be dropdown boxes for a pre-defined set of values, and would remember the value selected.

Any assistance on this would be greatly appreciated!

Edit: For clarification.

I have a spreadsheet which draws employee information from an external sheet and lists this information in a table. Additionally in that table are columns for tracking details about each employee's training. This data is added into the table manually.

The issue is that when the employee data is refreshed, the manually added data gets jumbled up. I believe it's to do with the way Excel is loading in the data and updating the table. What I need is to be able to refresh the data and have it keep the additional information in line with the correct employee.

Answer 1

I'm struggling with the same problem. I think I've got around the actual problem you've described, which occurs when your refresh introduces new records, by changing the properties of the connection.

If you select the table where the linked data is displayed and in the ribbon go to Data tab > Connection section and click Properties, a dialog will be displayed. I changed the action to be taken when the number of records changes upon refresh, as follows:

With this done, new rows seem to be inserted correctly for new records. The problem I'm now finding, though, is when you delete a record in the underlying data source. That still isn't handled properly, and the fact that the additional data you've entered which is local to your spreadsheet isn't linked in any way with the external data once again causes it to go wrong.

I also tried checking the "Include row numbers" box in that dialog, in the vain hope that that might somehow act as an internal unique identifier in the spreadsheet which would link the external data with the spreadsheet data, but to no avail. I found it interesting to add a column in my spreadsheet and number each row consecutively manually then delete a record from the external data source and refresh my spreadsheet. I can't determine from the way my manually entered row numbers get corrupted what on earth it's actually doing.

So, technically I think I've answered the actual question you asked, but it's not really enough of a solution to allow you to proceed, I don't think. Unless you'll never be deleting records in the underlying data source, in which case this might just work.