Adding Columns to Table that is linked to external data set

Copper Contributor

I have a connection to an external data set that I have brought into my workbook as a table.

I would like to add some new columns so users can enter data on the rows brought in from the external data set.

My issue is that once the external data is refreshed, the data that was entered into the new column is mis matched to the data coming from the connection.


For example, columns E, J, K, L & M do not exist in the external data set, the others do. If I refresh this data set, the data entered into these columns will longer align with the 'Job Name'.


Suggestions are appreciated!!

1 Reply



It's hard to know fully how to recommend you address this, without knowing more fully the "big picture"--what the full processes are of which this sheet is a part. Are there other sheets in the workbook? What do they contain, etc.?


Off the top of my head, purely from a database point of view, and just looking at the two pieces you've described, I think you could

  • keep the "added comments" in a separate sheet, using the unique (I trust it IS unique) Job No field as an identifier. 
  • keep the external data sheet in its own sheet as well -- you don't say how you're linking or importing or accessing that data -- but the basic point, whatever it is you're doing, is keep it intact as is
  • create a third sheet that pulls in the most current set of external data (perhaps using the FILTER function, if you have a current version of Excel) and then link the comments using XLOOKUP or the equivalent

(I do something like that with a workbook I have that brings in external data... but as I said, without actually learning more of your situation it's hard to be more specific on possible solutions)


Power Query might also serve to combine the two datasets. (I have no direct experience with Power Query, but suspect it could be used here)


Is it possible for you to post a copy of your workbook in OneDrive or GoogleDrive and grant access so I or others could see it?