Dynamic Link Formulas & Manual Entry Columns Misalign Upon Insert/Delete of Row on Linked Sheet
I am currently working on a project in which we have a large master sheet. This has served us to this point, but now the master sheet has grown so big and contains sensitive data that we would like to store elsewhere so that there is no over-exposure of data, but at the same time, leverage the data that already exists in the master. (because who wants to enter and maintain data in 2 places?!? 😁)
We tried dynamically linking and pulling columns from the master, using a unique index identifier formula. We added columns to the sheet to maintain the sensitive information. It almost worked.
The problem we saw was if anyone inserted a row on the Master sheet, the manually entered data on the linked sheet became misaligned by row. In other words, the data manually entered in a cell on a specific row, moved up or down a row, depending on where the insert happened on the master.
Is there a way to improve upon where we started to ensure that when someone adds or deletes rows from the master sheet, that integrity between linked data and manually entered data is maintained on the linked sheet?
Help Article Resources
Check out the Formula Handbook template!