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 cellL52
. Now,A3
displays: "next orange" - Originally,
A3
in "S2" referenced cellM51
from "S1".M51
usesMIN
formula to pull minimum dates fromL:L
in "S1" - After adding row in "S1", "S2" cell
A3
should referenceM52
from "S1" - However, "S2" cell
A3
referencesM51
(which now contains the wrong value)
What I've tried/why it won't work:
- 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
- didn't work: tried using absolute reference
$M$51
but doesn't reference correct cell after adding/removing rows - didn't work: modifying
IMPORTRANGE
withaddress
but doesn't reference correct cell after adding/removing rows - didn't work: tried using this Google Sheets script but got an error
MATCH
function: since my range is simply an individual cell (M51
), I cannot call out the range. cellM51
uses aMIN
function and won't be static; it can change as I add/remove rowsarray
: not sure how to do this withIMPORTRANGE
VLOOKUP
: created a tab in "S2" that populated the sheet from "S1" usingVLOOKUP
. however, when I reference myVLOOKUP
tab, it is still tied to the original referenced cells and does not update the reference (in "S2" cellD2
:='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?