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
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,
- Original Value in "S1" for
L51: "next orange"
- What I want to happen: After adding row in "S1", "S2"
A3updates reference to cell
A3displays: "next orange"
A3in "S2" referenced cell
MINformula to pull minimum dates from
- After adding row in "S1", "S2" cell
- However, "S2" cell
M51(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$51but doesn't reference correct cell after adding/removing rows
- didn't work: modifying
addressbut doesn't reference correct cell after adding/removing rows
- didn't work: tried using this Google Sheets script but got an error
MATCHfunction: since my range is simply an individual cell (
M51), I cannot call out the range. cell
MINfunction and won't be static; it can change as I add/remove rows
array: not sure how to do this with
VLOOKUP: created a tab in "S2" that populated the sheet from "S1" using
VLOOKUP. however, when I reference my
VLOOKUPtab, it is still tied to the original referenced cells and does not update the reference (in "S2" cell
='VLOOKUP of S1:"Added rows: Test w VLOOKUP"'!M51but I want it to update the formula to
=VLOOKUP of S1:"Added rows: Test w VLOOKUP"'!M50after adding 3 rows)
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
IMPORTRANGE when my column headers are different?