How do I anchor adjacent cells ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
Thanks for that. Will give it a try.

bpeltzer said:
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 said:
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?
Click to expand...
Click to expand...
 
Back
Top