Google-sheets – Associate cells to imported ones with IMPORTRANGE

google sheetsimportrange

I have table A with several columns and I use the following function to import some of data to table B:

=QUERY(IMPORTRANGE("0AuFgJ0VZkU8kdHdnU0N2Y3hFU3B6bkpjdEFsUEZOcXc"&"?"&now(),"A:BL"),"select Col5, Col6, Col7, Col8, Col9, Col3, Col4, Col15, Col16, Col17, Col18 where Col52 contains 'X'",1)

After that, I have several columns where I need to enter new data: from N to R for example. Let's say that when function is accomplished in table B I have three rows: 

A1: Ana
A2: Jacques
A3: Peter

In N2 I put something, for example French. If in table A I delete Ana, it is deleted in table B and A1 is Jacques and A2 is Peter. But French is still in N2, so now it is like Peter is English.

How to make N2 (French) to be associated with A2 (Jacques) so if Jacques moves to A1, French moves to N1 too?

Extra information posted by OP as 'Answer':

I need to keep the link with the source table because I need to add or remove records. So, Paste values only will not help. My source table is very bi, so I need in fact to extract some records and add new data on them. It is not really nationality, I just gave an example, but multiple columns. I mean that from one very big database I need to extract only those records that I need by putting X on a specific column and after that to work in another table only with the extracted ones.

Best Answer

I think you have two choices that would not suit you:

1) Put French into wherever you are importing from.
2) Once the range is imported, break the link (Copy, Paste special, Paste values only) so that deleting Ana in the source no longer causes her to disappear from the imported data.

Both the above can be avoided with a lookup table but to do so would be ‘ugly’ and if imported data is added to, rather than have parts deleted from it, could require a tedious amount of repetition. However it might be acceptable as a workaround.

Import to two different ranges and Copy, Paste special, Paste values only one of those. Add French to the converted version (the one no longer linked) and use that as a lookup table to place French where you would like, by searching for Jacques (and Ana and Peter) by cell reference from the linked version.

Related Topic