Example File: https://docs.google.com/spreadsheets/d/1Ht_2QMGMbHmlxSPoOiLX2vw78IL1wp3VhpOOc66eMqY/edit#gid=0

We are filling Point 1 - 4 manually. The data in A,B,C is sorted through C and will change every now and then. The problem I am noticing now is that A,B,C is moving. But D:G will stay in the same column.

We want to use this file to fill in the data since its our main file. So using the initial =query to also take into account D:G is not an option.

Would there be any other way to "link" D:G to the corresponding values in A:C?

CodePudding user response：

Looking at your sheet I noticed you try a VLOOKUP formula.
Please try the following formula

=INDEX(IFERROR(VLOOKUP(A1:A;Blad1!A2:I;{6\7\8\9};0)))

CodePudding user response：

Of course your approach would cause problems. You're trying to map manual data to some data that is bound to change. You can't expect the manual data to move or change in sync when the imported data changes.

You could probably make it work at least if the imported data does not change in order, and instead gets any new data appended. Even then, it doesn't help you if any of the imported rows gets deleted.

There are only two ways I could see to make this work:

1. Map your manual data as part of the original sheet where your other data is imported from. In other words, make D:G part of the source of A:C, if possible. This is the best approach. Works even when some imported rows get deleted or changed.
2. Don't sort A:C at source. Simply append new rows, and import as is. Keep your Blad1 sheet as the local source sheet, and add your manual data to D:G here. Then create a new sheet for sorting or do any other thing you'd like, and use this new sheet to feed your Blad2 sheet. This doesn't work if some of the imported rows get deleted or changed.