Excel 2000 - inserts in linked sheet not updating?

[Pheasant Plucker®]

Hi there,

Not even sure if the subject reflects what I am looking for so please bear
with me while I try to explain! ;^)

I have a summary spreadsheet that pulls its data from one or more separate
spreadsheets and it works well up to a point.

However if for example I insert an extra line in one of the separate
spreadsheets then the summary spreadsheet is not aware of the change so
therefore it is not updated.

Example;

In the summary sheet one cell contains the line;

='\\PATH\Spreadsheet1.xls]Sheet1'!$E$22

That is read in automatically & correctly whenever the summary spreadsheet
is opened and if the data in that particular cell in the separate
spreadsheet (in this case E22) is altered the change is subsequently altered
in the summary spreadsheet.

No problems there...

But if I insert a row in the separate spreadsheet so now cell E22 becomes
E23 the summary spreadsheet is not aware of the change and so therefore
cannot reflect the altered value.

How do I make the summary sheet aware that E22 has now become E23?

 

[Bernard Liengme]

I did this:
In Book2 I entered =[Book2]Sheet1!$D$1; it correctly displayed 5
Then I when to Book 1 and inserted a row above row 1
Returned to Book2 and the formula read =[Book2]Sheet1!$D$2 and displayed 5
(correctly)

So why do you have a different behaviour? If I had time I would experiment
with the insertion when Book2 was closed and see it this is the trouble.

best wishes
Bernard

 

[Pheasant Plucker®]

Thanks for the quick reply Bernard,

I had previously tried as you suggested - inserting a line in one of the
separate spreadsheets while the summary spreadsheet was closed but it didn't
work. :-(

I do have the separate spreadsheets in a different directory if that makes
any difference?

Thanks & regards,
-=pp=-

I did this:
In Book2 I entered =[Book2]Sheet1!$D$1; it correctly displayed 5
Then I when to Book 1 and inserted a row above row 1
Returned to Book2 and the formula read =[Book2]Sheet1!$D$2 and displayed 5
(correctly)

So why do you have a different behaviour? If I had time I would experiment
with the insertion when Book2 was closed and see it this is the trouble.

best wishes
Bernard

Hi there,

Not even sure if the subject reflects what I am looking for so please bear
with me while I try to explain! ;^)

I have a summary spreadsheet that pulls its data from one or more separate
spreadsheets and it works well up to a point.

However if for example I insert an extra line in one of the separate
spreadsheets then the summary spreadsheet is not aware of the change so
therefore it is not updated.

Example;

In the summary sheet one cell contains the line;

='\\PATH\Spreadsheet1.xls]Sheet1'!$E$22

That is read in automatically & correctly whenever the summary spreadsheet
is opened and if the data in that particular cell in the separate
spreadsheet (in this case E22) is altered the change is subsequently
altered
in the summary spreadsheet.

No problems there...

But if I insert a row in the separate spreadsheet so now cell E22 becomes
E23 the summary spreadsheet is not aware of the change and so therefore
cannot reflect the altered value.

How do I make the summary sheet aware that E22 has now become E23?

 

[Dave Peterson]

If that other workbook is closed, then it doesn't know that you inserted a
row--so any formulas in there won't be updated.

If they're both open, the formulas get adjusted.

If you don't have too many cells to fiddle with, you could name them
(insert|name|define).

the other workbook will find that range--no matter where you stuck it <vbg>.

Thanks for the quick reply Bernard,

I had previously tried as you suggested - inserting a line in one of the
separate spreadsheets while the summary spreadsheet was closed but it didn't
work. :-(

I do have the separate spreadsheets in a different directory if that makes
any difference?

Thanks & regards,
-=pp=-

I did this:
In Book2 I entered =[Book2]Sheet1!$D$1; it correctly displayed 5
Then I when to Book 1 and inserted a row above row 1
Returned to Book2 and the formula read =[Book2]Sheet1!$D$2 and displayed 5
(correctly)

So why do you have a different behaviour? If I had time I would experiment
with the insertion when Book2 was closed and see it this is the trouble.

best wishes
Bernard

Hi there,

Not even sure if the subject reflects what I am looking for so please bear
with me while I try to explain! ;^)

I have a summary spreadsheet that pulls its data from one or more separate
spreadsheets and it works well up to a point.

However if for example I insert an extra line in one of the separate
spreadsheets then the summary spreadsheet is not aware of the change so
therefore it is not updated.

Example;

In the summary sheet one cell contains the line;

='\\PATH\Spreadsheet1.xls]Sheet1'!$E$22

That is read in automatically & correctly whenever the summary spreadsheet
is opened and if the data in that particular cell in the separate
spreadsheet (in this case E22) is altered the change is subsequently
altered
in the summary spreadsheet.

No problems there...

But if I insert a row in the separate spreadsheet so now cell E22 becomes
E23 the summary spreadsheet is not aware of the change and so therefore
cannot reflect the altered value.

How do I make the summary sheet aware that E22 has now become E23?