vba - Excel: Update table across workbooks while protecting given column from being overwritten? -

i have file (let's call overlord.xlsm) contains table. in subfolders, have files called (overlord-north.xlsm, overlord-east.xlsm) etc. use data connections fetch table stored in overlord.xlsm.

within each sub-file, example overlord-north, want able enter information persistent when update table's data connection.

let's following contained in overlord.xlsm (col contains static autonumber using index):

col - col b - col c   1         2        

these contents echoed overlord-north, if enter in col c (from within overlord-north), information overwritten next time refresh information original file.

can stop this?

can lock columns in way prevent contents being overwritten, though part of table? or can choose omit columns data refresh query? literally solution allows me create persistence given cells or columns in externally fetched table of interest.

and yes, it's necessary columns part of table, otherwise aren't sortable index key, prerequisite. can't keep information in single file, have been choice months ago if @ possible.

i have workaround in mind, it's going kill resource effectiveness... i'll appreciate input on locking columns, or if that's not possible, suggestions improving stability , speed of column-preservation-array-maker.

sub rewritestatus() ' loop through array, match index number between array , sheet, , write corresponding cell(s) table. end sub   sub preservestatus()     dim statusarray() string, emptyrow boolean      redim preserve statusarray(1 4, 1 2)         emptyrow = true      = 2 listobject("push_table").count         if cells(i, 14) <> ""             statusarray(i, 2) = cells(i, 14).value             emptyrow = false         end if         if cells(i, 15) <> ""             statusarray(i, 3) = cells(i, 15).value             emptyrow = false         end if         if cells(i, 17) <> ""             statusarray(i, 4) = cells(i, 17).value             emptyrow = false         end if         ' redim array if cell of row found non-empty         ' , copy index key row         if emptyrow = false              redim preserve statusarray(1 2, 1 ubound(statusarray, 2) + 1)             statusarray(i, 1) = cells(i, 1).value         end if     next end sub