Reference & update destin. cells on 1st machine from source workbook on 2nd machine.

[eastside877]

Hi, I have a question about linking and updating cells referencing
another workbook on another machine on a home network.

I have a workbook opened on machine A on my home network. On a second
machine B located on the same network, I have a second workbook opened
and on this workbook B I want a range of cells to reference a range of
cells in workbook A and update in real time that destination range on
workbook B (if it is possible in this case).

I have formatted the destination each destination cell on machine B
using the formula
='\\SourceMachineA\directory:\[WorkbookA.xls]Sheet1'!A25

When I enter the formula I get prompted with an "Update values" dialog
box and after pointing it to the source workbook, the value in the cell
gets updated correctly; so I know that the formula is formatted
correctly insofar as it points to the right location. However, this is
the only time when I can get the value to update

I have also tried by mapping the source drive on the destination
machine and using that in the formulas, but it doesn't work any better.


Is it possible to do what I am trying to do (directly reference a cell
from another workbook on a second machine located on the local network)
and if yes, what am I doing wrong? If it is impossible, what are the
alternatives to accomplish the task?

All help and suggestions would be much appreciated. Thanks in advance,

Joe

 

[tony h]

I am not sure if you have made the problem clear.
What I understand is :
- you have managed to link the cells correctly you are unhappy that
this just refreshes just on opening the file.

- what you seem to be asking is for a user on the other machine to
update the workbook and for those updates to appear in your
workbook.on't think it would be possible for those updates to show
unless the other person saves the workbook.

A bit more description about what is going on and why you need to do it
should be possible to find a solution.

regards

 

[eastside877]

Hi. Thanks for the reply. My goal is to have the destination cells to
update in more-or-less real time to the content of the source cells:
this while both workbooks are open and without having to save the
source workbook. I would seem from what you say that this is not
possible.

I gave a shot at the data menu to establish connections to retrieve
data , but the Wizard seems to require source data formatted as a
table.
I'm rather out of my depth in that case.

Joe

 

[tony h]

Although technically it is possible to keep the spreadsets synchronise
without saving it is getting into serious programming and I wouldn'
recommend it.

Why don't you want to save the spreadsheet? Saving the spreadsheet ca
be automated to happen either at regular intervals or on specifi
events.

regard

 

[eastside877]

Saving the spreadsheet every few seconds or so could be done with a
timer I suppose but it sounds like I've been looking at a bad solution
in attempting direct linking. Would DDE server be an option?

Thanks,

Joe

 

[tony h]

Things to consider :
- are the updates are two way or one way.
- how often is data being updated
- do you know when you need to refresh the data
- are you only looking at a few cells or larger amounts of data.
- does the worksheet layout change - is this limited to adding new
rows?
- is the other spreadsheet always open (is the other machine always
available) what do you do if these aren't available.

It is these sort of questions that help to determine an apropriate
solution

regards,

 

[eastside877]

Hi. The updates are one-way only to the dest spreadsheet.
The source data is updated a couple of time per seconds. If it's
synchronized to the dest spreadsheet it would be great but if it's
updated every 2 or 3 seconds it would work too.
I'm looking at 30 to 60 cells max.
The layout at both ends is limited to adding new rows
The source spreadsheet is normally open while I use the dest
spreadsheet, but I need to be able to use the dest even if the source
is closed.
I would get the data into a separate sheet on my dest workbook, and
manipulate it from there.

Thanks,

Joe