Linking Workbooks

[Tendresse]

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

 

[JP]

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

 

[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]

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

 

[Tendresse]

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]

That might explain it <GRIN>!


--JP

 

[Tendresse]

Hi JP .. well, i'm still having a little glitch!
Everything works perfectly as long as both workbooks are open at the same
time. However, if i add the new row in the source workbook, then open the
Master workbook afterwards, the cells containing the formulas didn't shif!
So i tried something different:
1) In the 'Main Data' worksheet of the original workbook, i defined a name
for cell BT14, i called it "Totals".
2)Then in the 'Summary' worksheet of that workbook, i linked cell A1 to
"Totals" using the formula: =Totals
3) Then i created a copy of the Summary worksheet in the Master workbook as
you described in your previous reply.
4) Things worked very well until i closed Master and then added a row in the
'Main Data' in the source
5) when i open the Master, cell A1 still says: =Totals, however its content
is that of cell BT14 of the 'Summary' when it should be BT15 given that it
moved after adding the new row!!!
I'm really puzzled and can't find a logical explanation to this .... i can't
guarantee that the Master workbook will always be open while the others are
open ... what do i do to solve this problem? what's the use of linking
worksheets if everything goes out of whack if you simply add a row?!!!
Please help ..
Many thanks in advance ..

 

[JP]

Why not have links from the master sheet directly to the 'Main Data'
worksheets in the 10 separate workbooks? Maybe the roundabout way you
are putting links into the master worksheet is causing them to break.


HTH,
JP