Hi all,
I need some help with linking workbooks, please. Here is the question in
bullet points so you dont get confused:
- I have 10 spreadsheets each one is saved on a different server.
- Each one of these 10 spreadsheets has 2 worksheets: €˜Main Data and
€˜Summary
- The €˜Summary worksheet has cells linked to other cells in the €˜Main Data
worksheet. For example: cell B8 in €˜Summary has the following formula:
='Main Data'!$BT$123
- I created a new spreadsheet and called it €˜Master
- I went to each €˜Summary worksheets in the other 10 spreadsheets and
created a new copy of each one of them in €˜Master (So 'Master' has 10
worksheets - a copy of each 'Summary')
- Things worked so good for a while. For example, the value in cell B8 in
the €˜Summary (in €˜Master as well as in the original spreadsheet) gets
updated every time the value in €˜Main Data!BT123 changes.
- However, my happiness didnt last long. The moment I added a new row in
the €˜Main Data worksheet. The following happened:

When a new row is added to €˜Main Data and cell BT123 gets shifted one step
down, this gets updated automatically in the €˜Summary worksheet located in
the same spreadsheet and the formula in B8 then becomes as follows: ='Main
Data'!$BT$124

However, the correspondent €˜Summary in the €˜Master doesnt get updated the
same way. The formula in there remains linked to cell BT123 instead of BT124.

What am I doing wrong?
Im using Excel 2003.
I would really appreciate any help or any clues or any other solution to
enable me having the 10 summaries in a separate spreadsheet.
Tendresse

I tested your problem out with a sample of three workbooks and was not
able to duplicate the error. When I inserted a row, all of the
formulas shifted to $BT$124 as expected. Did you check all of the
formulas to make sure they are absolute references?

--JP


On Feb 27, 7:15 pm, Tendresse
wrote:
Hi all,
I need some help with linking workbooks, please. Here is the question in
bullet points so you don't get confused:
- I have 10 spreadsheets each one is saved on a different server.
- Each one of these 10 spreadsheets has 2 worksheets: 'Main Data' and
'Summary'
- The 'Summary' worksheet has cells linked to other cells in the 'Main Data'
worksheet. For example: cell B8 in 'Summary' has the following formula:
='Main Data'!$BT$123
- I created a new spreadsheet and called it 'Master'
- I went to each 'Summary' worksheets in the other 10 spreadsheets and
created a new copy of each one of them in 'Master' (So 'Master' has 10
worksheets - a copy of each 'Summary')
- Things worked so good for a while. For example, the value in cell B8 in
the 'Summary' (in 'Master' as well as in the original spreadsheet) gets
updated every time the value in 'Main Data!BT123' changes.
- However, my happiness didn't last long. The moment I added a new row in
the 'Main Data' worksheet. The following happened:

When a new row is added to 'Main Data' and cell BT123 gets shifted one step
down, this gets updated automatically in the 'Summary' worksheet located in
the same spreadsheet and the formula in B8 then becomes as follows: ='Main
Data'!$BT$124

However, the correspondent 'Summary' in the 'Master' doesn't get updated the
same way. The formula in there remains linked to cell BT123 instead of BT124.

What am I doing wrong?
I'm using Excel 2003.
I would really appreciate any help or any clues or any other solution to
enable me having the 10 summaries in a separate spreadsheet.
Tendresse

As expected, the formulas shifted to $BT$124 in the 'Summary' worksheet
located in the original spreadsheets (the 10 spreadsheets) .. However, in the
copy of that same 'Summary' that i made in the 'Master' spreadsheet the
formula didn't shift. In the 'Master' spreadsheet the formula still refers to
$BT$123.

isn't the '$' sign an indication that it's absolute references?

I would really appreicate any help.

"JP" wrote:

I tested your problem out with a sample of three workbooks and was not
able to duplicate the error. When I inserted a row, all of the
formulas shifted to $BT$124 as expected. Did you check all of the
formulas to make sure they are absolute references?

--JP


On Feb 27, 7:15 pm, Tendresse
wrote:
Hi all,
I need some help with linking workbooks, please. Here is the question in
bullet points so you don't get confused:
- I have 10 spreadsheets each one is saved on a different server.
- Each one of these 10 spreadsheets has 2 worksheets: 'Main Data' and
'Summary'
- The 'Summary' worksheet has cells linked to other cells in the 'Main Data'
worksheet. For example: cell B8 in 'Summary' has the following formula:
='Main Data'!$BT$123
- I created a new spreadsheet and called it 'Master'
- I went to each 'Summary' worksheets in the other 10 spreadsheets and
created a new copy of each one of them in 'Master' (So 'Master' has 10
worksheets - a copy of each 'Summary')
- Things worked so good for a while. For example, the value in cell B8 in
the 'Summary' (in 'Master' as well as in the original spreadsheet) gets
updated every time the value in 'Main Data!BT123' changes.
- However, my happiness didn't last long. The moment I added a new row in
the 'Main Data' worksheet. The following happened:

When a new row is added to 'Main Data' and cell BT123 gets shifted one step
down, this gets updated automatically in the 'Summary' worksheet located in
the same spreadsheet and the formula in B8 then becomes as follows: ='Main
Data'!$BT$124

However, the correspondent 'Summary' in the 'Master' doesn't get updated the
same way. The formula in there remains linked to cell BT123 instead of BT124.

What am I doing wrong?
I'm using Excel 2003.
I would really appreciate any help or any clues or any other solution to
enable me having the 10 summaries in a separate spreadsheet.
Tendresse

All I can suggest at this point is that you recreate the Summary
worksheets in the Master workbook by doing the following:

1. Move the Summary worksheets from the Master workbook to a backup
workbook.
2. Open all 10 original Summary worksheets/books.
3. Right click on the Summary tab of each worksheet, choose "Move or
Copy..."
4. Select your master worbook from the dropdown box, select "(move to
end)" in the listbox and click "Create a copy" checkbox.

That is how I tested your example so maybe if you follow the same
steps you won't have the same problem.


HTH,
JP


On Feb 27, 9:16*pm, Tendresse
wrote:
As expected, the formulas shifted to $BT$124 in the 'Summary' worksheet
located in the original spreadsheets (the 10 spreadsheets) .. However, in the
copy of that same 'Summary' that i made in the 'Master' spreadsheet the
formula didn't shift. In the 'Master' spreadsheet the formula still refers to
$BT$123.

isn't the '$' sign an indication that it's absolute references?

I would really appreicate any help.



"JP" wrote:
I tested your problem out with a sample of three workbooks and was not
able to duplicate the error. When I inserted a row, all of the
formulas shifted to $BT$124 as expected. Did you check all of the
formulas to make sure they are absolute references?

Hi JP,
I tried what you said now with one of the 10 spreadsheets and it seems to be
working fine! Surprise Surprise. Even though that's exactly how i did it the
first time. The only difference is that the first time i had the 10
spreadsheets open as Read-Only when i created the copy of their 'Summary'. Do
you think this would have been the cause?! I do'nt think this would have
anything to do with it.
Anyway, i'll continue with the other 9 and will keep on eye on it. If you
don't hear back from me, then it's working fine.
Thanks for all your help .. have a great day ..


"JP" wrote:

All I can suggest at this point is that you recreate the Summary
worksheets in the Master workbook by doing the following:

1. Move the Summary worksheets from the Master workbook to a backup
workbook.
2. Open all 10 original Summary worksheets/books.
3. Right click on the Summary tab of each worksheet, choose "Move or
Copy..."
4. Select your master worbook from the dropdown box, select "(move to
end)" in the listbox and click "Create a copy" checkbox.

That is how I tested your example so maybe if you follow the same
steps you won't have the same problem.


HTH,
JP


On Feb 27, 9:16 pm, Tendresse
wrote:
As expected, the formulas shifted to $BT$124 in the 'Summary' worksheet
located in the original spreadsheets (the 10 spreadsheets) .. However, in the
copy of that same 'Summary' that i made in the 'Master' spreadsheet the
formula didn't shift. In the 'Master' spreadsheet the formula still refers to
$BT$123.

isn't the '$' sign an indication that it's absolute references?

I would really appreicate any help.



"JP" wrote:
I tested your problem out with a sample of three workbooks and was not
able to duplicate the error. When I inserted a row, all of the
formulas shifted to $BT$124 as expected. Did you check all of the
formulas to make sure they are absolute references?

That might explain it <GRIN!


--JP

On Feb 27, 11:03*pm, Tendresse
wrote:
Hi JP,
I tried what you said now with one of the 10 spreadsheets and it seems to be
working fine! Surprise Surprise. Even though that's exactly how i did it the
first time. The only difference is that the first time i had the 10
spreadsheets open as Read-Only when i created the copy of their 'Summary'. Do
you think this would have been the cause?! I do'nt think this would have
anything to do with it.
Anyway, i'll continue with the other 9 and will keep on eye on it. If you
don't hear back from me, then it's working fine.
Thanks for all your help .. have a great day ..