Creating a dynamic copy of another Google Sheet, in which new columns could always be linked to the dynamic copy

Question

In a dynamic copy of an existing spreadsheet (that is also updated with new lines), I made a dynamic copy in another spreadsheet, in which I added columns. All good here.

I use the new columns to complete the information from the dynamic copy, but when a new line is added in the original document, it takes the information I had for the previous line :

Example :

A2 is the new column is the final document
B2 to D2 is the dynamic copy I inserted in that final document
I fill that A2 with information I need to complete B2 to D2
A line is the added in the original document and becomes A2 now
A2 in the final document is the added line, and all the information that I added for the previous A2 complete the wrong line.

Do you know how I can solve this ?

Answer 1

Because you are introducing static information (typing info into A2) with dynamic information (B2:D2), there is no way for sheets to know to keep this tied together. The short answer is, you need to make a formula to look up or calculate against your dynamic table. This will then keep your A column updated no matter what else enters your raw data.

2 options:

1. Put the information that you would put in A2 on the final sheet in your raw data. Then when you port it to your final doc they it will always update. It sounds like there is a reason why you don't want to do this though that you haven't mentioned.

2. Do you have a unique field, or combination of conditions in B2:D2 that would tell you what you need to put in A2 automatically? If so then just create a formula / look up list. Then rather than manually typing in A2, A2 would include a formula to look up or calculate the value you want in there. That is, you are now combining a dynamic formula with the dynamic data you are inserting. Whenever you update your raw data, the final doc updates with the new line (new A2) and then your formula will calculate the appropriate look up.

If you need a more specific answer, share a sample of your data with what you intend to put in column A. Leveraging functions like array_constrain() and arrayformula() could make this fully automatic to every new line that is entered.