How do I anchor adjacent cells ?

[Roganjosh]

Col A of spreadsheet (the recipient) contains Anchored cell refs to another
workbook (the source), and then I enter info in Col B which relates only to
the cell in Col A (eg B1 to A1 etc).
If I later insert rows within the data of the source workbook, and update
the recipient spreadsheet upon opening it, the data in Col B no longer
matches the cell in Col A. Is there any way of keeping the relationship
between the adjacent cells, ie some form of anchoring, so that this does not
happen?

 

[bpeltzer]

Two thoughts:
1) make sure both spreadsheets are open when you change the structure of the
source workbook
or 2) don't used fixed column references, but rather determine them
dynamically using a match function. For instance, if you have column headers
Jan Feb Mar, etc, and you want to find the data for Feb,
=match(Feb,[Source.xls]Sheet1!1:1,false) would give you the column, which you
could then use in an address or offset function.

 

[Roganjosh]

Thanks for that. Will give it a try.

Two thoughts:
1) make sure both spreadsheets are open when you change the structure of the
source workbook
or 2) don't used fixed column references, but rather determine them
dynamically using a match function. For instance, if you have column headers
Jan Feb Mar, etc, and you want to find the data for Feb,
=match(Feb,[Source.xls]Sheet1!1:1,false) would give you the column, which you
could then use in an address or offset function.

Col A of spreadsheet (the recipient) contains Anchored cell refs to another
workbook (the source), and then I enter info in Col B which relates only to
the cell in Col A (eg B1 to A1 etc).
If I later insert rows within the data of the source workbook, and update
the recipient spreadsheet upon opening it, the data in Col B no longer
matches the cell in Col A. Is there any way of keeping the relationship
between the adjacent cells, ie some form of anchoring, so that this does not
happen?