How do I anchor adjacent cells ?
 

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?

How do I anchor adjacent cells ?
 

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" wrote:

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?

How do I anchor adjacent cells ?
 

Thanks for that. Will give it a try.

"bpeltzer" wrote:

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" wrote:

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?