linked workbooks

[dmk]

Each month I update my "invoice register" which is linked
to 4 other Excel workbooks and each workbook is
automatically updated. My problem occurs when I have to
insert a new row in the invoice register and the
appropriate workbook. This throws off my figures by
pulling the $value from the row above.
ie. co. Jan Feb Mar
ABC $10 $12 $15
LBK $23 $17 $18
JFK $15 $19 $19
If I add another row between ABC and LBK, in the invoice
register, my linked file still pulls info from the
original cell location. What am I doing wrong???
Thanks in advance.
Dianne

 

[hgrove]

dmk wrote...

Each month I update my "invoice register" which is linked to 4
other Excel workbooks and each workbook is automatically
updated. My problem occurs when I have to insert a new row in
the invoice register and the appropriate workbook. This throws
off my figures by pulling the $value from the row above. ...
If I add another row between ABC and LBK, in the invoice
register, my linked file still pulls info from the original cell
location. What am I doing wrong???

You're not doing anything wrong. This is how Excel is intended to wor
when you insert rows. Unfortunately this means the way you've designe
your invoice application is fragile and can't support adding data b
inserting rows.

If the formulas in your other workbooks are accessing cells in you
invoice workbook directly, e.g.,

[OtherWorkbook.xls]Whatever!BB5:
=[INVOIVE.xls]SomeSheet!B5

then you may be able to fix this by replacing direct cell to cel
references with INDEX function calls. For instance, if you wanted t
insert a new row 5 in [INVOIVE.xls]SomeSheet, you could change th
formula in [OtherWorkbook.xls]Whatever!BB5 to

[OtherWorkbook.xls]Whatever!BB5:
=INDEX([INVOIVE.xls]SomeSheet!B:B,ROW())

This new formula would *always* reference the cell in col B i
[INVOIVE.xls]SomeSheet in the same row as itself. You could ad
constants to the index to adjust for differing starting rows, so if ro
2 of [OtherWorkbook.xls]Whatever should reference row 5 o
[INVOIVE.xls]SomeSheet, change this as follows.

[OtherWorkbook.xls]Whatever!BB2:
=INDEX([INVOIVE.xls]SomeSheet!B:B,ROW()+3