Google-sheets – Dynamic updating referenced cell from another workbook after adding/deleting rows using IMPORTRANGE – use ARRAY or VLOOKUP

formulasgoogle sheetsgoogle-sheets-arrayformulaimportrangevlookup

I add/deleted/move rows frequently in my workbooks. When I add/remove rows from Spreadsheet#1 ("S1"), I need the referenced cell in Spreadsheet#2 ("S2") to dynamically update so it still references the correct value, even though the cell itself has changed. Cell values may change as they update based on a MIN formula.

I've used the IMPORTRANGE function to populate the info from "S1" into "S2".
The range from "S1" is actually just a specific cell in "S1".

IMPORTRANGE works fine when the values change. However, the issue arises when I add/delete a row. It references the same cell from "S1" (cell M51), which is now blank, instead of automatically updating its reference to the correct cell (after adding a row, M51 becomes M52).

Example:

  • Original Value in "S1" for L51: "next orange"
  • What I want to happen: After adding row in "S1", "S2" A3 updates reference to cell L52. Now, A3 displays: "next orange"
  • Originally, A3 in "S2" referenced cell M51 from "S1". M51 uses MIN formula to pull minimum dates from L:L in "S1"
  • After adding row in "S1", "S2" cell A3 should reference M52 from "S1"
  • However, "S2" cell A3 references M51 (which now contains the wrong value)

What I've tried/why it won't work:

  1. duplicate/copy sheet from S1 to S2: redundancy. I want S2 to dynamically update when I update S1. don't want to update both S1 and S2 each time
  2. didn't work: tried using absolute reference $M$51 but doesn't reference correct cell after adding/removing rows
  3. didn't work: modifying IMPORTRANGE with address but doesn't reference correct cell after adding/removing rows
  4. didn't work: tried using this Google Sheets script but got an error
  5. MATCH function: since my range is simply an individual cell (M51), I cannot call out the range. cell M51uses a MIN function and won't be static; it can change as I add/remove rows
  6. array: not sure how to do this with IMPORTRANGE
  7. VLOOKUP: created a tab in "S2" that populated the sheet from "S1" using VLOOKUP. however, when I reference my VLOOKUP tab, it is still tied to the original referenced cells and does not update the reference (in "S2" cell D2: ='VLOOKUP of S1:"Added rows: Test w VLOOKUP"'!M51 but I want it to update the formula to =VLOOKUP of S1:"Added rows: Test w VLOOKUP"'!M50 after adding 3 rows)

Test docs:

Spreadsheet1: S1 please make a copy of "Sheet7" tab

Spreadsheet2: S2 please make a copy of "TO EDIT" tab

Would appreciate some assistance figuring this out. I need M50:M59 (from "S1") to populate into A2:A11 ( in "S2"). My actual source spreadsheet (not linked) has tons of data (range A1:AF70) and I add/delete/move rows often.

If I add a row to "S1", how can I ensure M51 will update in "S2" to M52? Is there a way to combine ARRAY or VLOOKUP with IMPORTRANGE when my column headers are different?

Best Answer

this can be achieved by adding an extra column far away, where it can be hidden and then populating this column by joining desired set of cells by unique separator until split will occur on the second spreadsheet. note that:

  • adding or deleting rows will not affect dynamicity of IMPORTRANGE
  • adding deleting columns will break all imported data
  • there is no need for an extra column if there is a unique separator per every IMPORTRANGE of data and the search is applied always to such unique separator

in this particular case, there was used column AG from which IMPORTRANGE was fed.

in Spreadsheet1 in Sheet1!AG (no matter of row number) there are formulas which JOIN content of L50 and M50 as well as the content of L51 and M51, etc... (no matter if it's done directly or indirectly as far as the output is TEXT):

=JOIN("¤"; L50; MIN(FILTER(L:L; ISNUMBER(SEARCH("*banana*"; P:P))     
                               +ISNUMBER(SEARCH("*banana*"; Q:Q))
                               +ISNUMBER(SEARCH("*banana*"; R:R)))))

outputing: next banana¤30-Aug-2004

=JOIN("¤"; L51; MIN(FILTER(L:L; ISNUMBER(SEARCH("*orange*"; P:P))     
                               +ISNUMBER(SEARCH("*orange*"; Q:Q))
                               +ISNUMBER(SEARCH("*orange*"; R:R)))))

outputing: next orange¤2-Oct-2003

=JOIN("♥"; L52; AVERAGE(FILTER(L:L; ISNUMBER(SEARCH("orange"; P:P))
                                   +ISNUMBER(SEARCH("orange"; Q:Q))
                                   +ISNUMBER(SEARCH("orange"; R:R)))))

outputing: X♥25-Sep-2013

=JOIN("♀"; L53; MIN(FILTER(L5:L48; ISNUMBER(SEARCH("*banana*"; Q5:Q48))
                                  *ISNUMBER(SEARCH("open";     R5:R48)))))

outputing: next banana♀20-Aug-2000

=JOIN("♂"; L54; AVERAGEIFS(M5:M48; R5:R48; "open", 
                                   Q5:Q48; "*banana*"))

outputing: avg days open (banana)♂74.41

=JOIN("♪"; L55; Q50/Q51)

outputing: util♪0.370544987

=JOIN("♫"; L56; MINIFS(M5:M48; R5:R48; "open", 
                               Q5:Q48; "*banana*"))

outputing: newest (mo)♫3.48

=JOIN("¤"; L57; M56*30.5)

outputing: newest(days)¤106.2580645

=JOIN("♤"; L58; M58)

outputing: avg LMT♤25051.35484

at this point, it doesn't matter if the format of joined cells is outputting elsehow (eg. 2nd part of the output should be formatted as $, %, mm/dd/yyyy) because in Spreadsheet2 after splitting you can format it back as you wish

in Spreadsheet2 you are free to paste following formula at any column and any row as well as you are free to:

  • add or delete any rows in Spreadsheet1
  • and add or delete any rows or columns in Spreadsheet2
 =SPLIT(
        ARRAY_CONSTRAIN(
                        QUERY(


        IMPORTRANGE("13evadbMLzvQVSGbYssn_0deFdcmb5l3sqpeFgcNTjOY"; "'Sheet1'!AG1:AG1000");

        "select Col1 where Col1 ='"&
        FILTER(

        IMPORTRANGE("13evadbMLzvQVSGbYssn_0deFdcmb5l3sqpeFgcNTjOY"; "'Sheet1'!AG1:AG1000");

                   ISNUMBER(
                            SEARCH("banana";

        IMPORTRANGE("13evadbMLzvQVSGbYssn_0deFdcmb5l3sqpeFgcNTjOY"; "'Sheet1'!AG1:AG1000"))

                   ))
                                    &"'"); 
                        1; 1); 
       "¤"; 1; 0)

this basically SEARCHes for text value "banana" in Spreadsheet1 under Sheet1 from range AG1:AG1000 and feed it to the FILTER which feeds criterion of QUERY which is ARRAY_CONSTRAINed to return one entry and that entry is SPLIT after unique separator "¤" (used earlier in JOIN) into two columns at the same row. and that's it.

if the content of cell L50 is static like banana and also unique per column you can SEARCH for "banana" otherwise you need to use unique separator per column and SEARCH for such separator instead of "banana"

for a successful linkup, you need to be sure that separator in SPLIT matches separator in JOIN ("¤"). you can use any symbol you wish as the separator (http://www.i2symbol.com/symbols)

example: for formula =JOIN("♤"; L58; M58) you can use:

 =SPLIT(
        ARRAY_CONSTRAIN(
                        QUERY(


        IMPORTRANGE("13evadbMLzvQVSGbYssn_0deFdcmb5l3sqpeFgcNTjOY"; "'Sheet1'!AG1:AG1000");

        "select Col1 where Col1 ='"&
        FILTER(

        IMPORTRANGE("13evadbMLzvQVSGbYssn_0deFdcmb5l3sqpeFgcNTjOY"; "'Sheet1'!AG1:AG1000");

                   ISNUMBER(
                            SEARCH("lmt";

        IMPORTRANGE("13evadbMLzvQVSGbYssn_0deFdcmb5l3sqpeFgcNTjOY"; "'Sheet1'!AG1:AG1000"))

                   ))
                                    &"'"); 
                        1; 1); 
       "♤"; 1; 0)

or

 =SPLIT(
        ARRAY_CONSTRAIN(
                        QUERY(


        IMPORTRANGE("13evadbMLzvQVSGbYssn_0deFdcmb5l3sqpeFgcNTjOY"; "'Sheet1'!AG1:AG1000");

        "select Col1 where Col1 ='"&
        FILTER(

        IMPORTRANGE("13evadbMLzvQVSGbYssn_0deFdcmb5l3sqpeFgcNTjOY"; "'Sheet1'!AG1:AG1000");

                   ISNUMBER(
                            SEARCH("♤";

        IMPORTRANGE("13evadbMLzvQVSGbYssn_0deFdcmb5l3sqpeFgcNTjOY"; "'Sheet1'!AG1:AG1000"))

                   ))
                                    &"'"); 
                        1; 1); 
       "♤"; 1; 0)

or

 =SPLIT(
        ARRAY_CONSTRAIN(
                        QUERY(


        IMPORTRANGE("13evadbMLzvQVSGbYssn_0deFdcmb5l3sqpeFgcNTjOY"; "'Sheet1'!AG1:AG1000");

        "select Col1 where Col1 ='"&
        FILTER(

        IMPORTRANGE("13evadbMLzvQVSGbYssn_0deFdcmb5l3sqpeFgcNTjOY"; "'Sheet1'!AG1:AG1000");

                   ISNUMBER(
                            SEARCH("avg LMT";

        IMPORTRANGE("13evadbMLzvQVSGbYssn_0deFdcmb5l3sqpeFgcNTjOY"; "'Sheet1'!AG1:AG1000"))

                   ))
                                    &"'"); 
                        1; 1); 
       "♤"; 1; 0)
Related Topic