Referencing cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sel - 09 Jan 2008 11:03 GMT

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!)

Reply to this Message

papou - 09 Jan 2008 12:08 GMT

Hello

Use either Defined names or absolute references.

HTH
Cordially
Pascal

> I've got two workbooks linked together. Workbook A gets information from
> workbook B by referencing cells. This works well until someone enters a
[quoted text clipped - 5 lines]
>
> (I apologise if this doesn't make very much sense!)

Reply to this Message

Sel - 10 Jan 2008 09:23 GMT

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

> Hello
>
[quoted text clipped - 13 lines]
> >
> > (I apologise if this doesn't make very much sense!)

Reply to this Message

papou - 10 Jan 2008 12:00 GMT

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

> Thankyou, how do I do this? and what's an absolute reference? (sorry my
> understanding of Excel is lacking!)
[quoted text clipped - 18 lines]
>> >
>> > (I apologise if this doesn't make very much sense!)

Reply to this Message

JE McGimpsey - 09 Jan 2008 12:15 GMT

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)

> I've got two workbooks linked together. Workbook A gets information from
> workbook B by referencing cells. This works well until someone enters a
[quoted text clipped - 4 lines]
>
> (I apologise if this doesn't make very much sense!)

Reply to this Message