Referencing cells

[Sel]

I've got two workbooks linked together. Workbook A gets information from
workbook B by referencing cells. This works well until someone enters a
column into the middle of workbook B and workbook A picks up the wrong
information from workbook B. Is there anyway to get workbook A to understand
that a column has been inserted into workbook B and that it just needs to
shift the references along one column?

(I apologise if this doesn't make very much sense!)

 

[papou]

Hello

Use either Defined names or absolute references.

HTH
Cordially
Pascal

 

[JE McGimpsey]

One way:

Name the column in Workbook B (say, "MyCol"), then reference the name,
e.g.:

=INDEX('<your path to B>Workbook B.xls'!MyColumn, 3)

 

[Sel]

Thankyou, how do I do this? and what's an absolute reference? (sorry my
understanding of Excel is lacking!)

 

[papou]

Hello
Absolute references: When referencing to cells use $ symbol to "freeze"
column and row, eg:
=$A$1
But I suspect this won't help in your case and I would recommend that you
use defined names instead.
To define a name:
1) Select the cells in workbook B you want to refer to.
2) Go to Insert>Name>Define
3) Input a name eg "MyName" and validate
Repeat this for all the cells you need to refer to.

Then in workbook A, your formula will look like this:
='C:\Documents and Settings\Sel\DeskTop\BookB.xls'!MyName

HTH
Cordially
Pascal