LINKED WORKBOOKS - TOTAL CONFUSION

[DR Hall]

I have two workbooks with hundreds of cells linked from one to the other.
Each workbook is the 'Source' for the other. One provides a USER interace
INPUT and OUTPUT, and the other does all the calculations.

Both the workbooks were created on my laptop and started of as one file, but
after reaching the 'Too many Cell formats' error, I split the workbook into
two.
I use the Add-Ins Analysis Toolpak and Analysis Toolpak VBA and so Excel is
set up accordingly, in the Options dialogue box I have Automatic calculation
checked, Update remote references checked, save external link values checked,
Ask for Automatic links checked, macro security set to Medium, and in the
Edit Links dialogue box the Automatic updates button is checked. I have
hidden the calculation spreadsheet, but it is still open and saved it though
VBA editor in the hidden state. Then I saved the whole workspace (*.xlw).
When I open the workspace both the workbooks open as expected and the links
update perfectly - So I am happy. Unfortunately when the same workspace is
opened on another machine with (the same version of) Excel set up in exactly
the same way as my computer the links do not find each other and no cell
links are updated in either direction between the worksheets. Even the Cell
that contains the =CELL("filename") returns a #Value. If then I open it again
on my computer it works fine. I have checked all the Add-Ins, all the Options
both general and in the Edit Links dialogue boxes and now I am stuck, I have
tried everything I know, both worksheets are open but don't update each
other, I check the formula and they are all fine, and to the best of my
knowledge should work. It has taken me 3 months of work to get this far, and
now I find that unless my company uses my laptop to open the spreadsheet(s)
it is useless! Please help! There must be some difference between how excel
is set up on different machines tyhat I am not aware of!!!!

 

[Barb Reinhardt]

I believe that for links to display properly, you need to have them both
opened on your machine. Someone else can verify this for me.

 

[DR Hall]

Both of the sheets are open. One is in a hidden state (Visible=FALSE) and one
is Visible (Visible=TRUE). But both are open, I checked the Status in the
Edit Links dialogue box, again it works on my computer but not on others???
Another error that is occuring and am currently trying to understand is that
on my computer if I enter the formula =CELL("filename") into a cell and save
the workbook the filename is correctly displayed in the cell. On all the
other computers in my office, this same formula return #Value even after the
file had been saved and a forced worksheet calculation is undertaken? If you
have any other thought I would be very grateful!