Stubborn Link Data/Value Problem - Help Appreciated!

Hello,


I am having a problem with a link in one of my Excel worksheets and
I'm hoping that someone can help me with it. Here's the situation...

I have two separate workbooks that are connected via a link such that
an update in one workbook is automatically (and immediately) reflected
in the second book. There is a cell (say A1) in the second book (the
destination) that contains a link with the following format:

=Excel.Sheet.8|'C:\Folder\Source.xls'!'!Sheet1!R2C 1'

[I created this link by copying the source cell from the source
workbook and pasting (using Paste_Special) in the destination cell in
the destination workbook.]

NOW...this part works great! No problems at this stage. An update in
the Source workbook is reflected in the Destination workbook just as I
want it.

BUT...here's the problem. I have a cell, A5, in the destination
workbook that is supposed to perform a calculation based on the value
in cell A1 (the cell containing the link). Unfortunately, even though
there is a numeric value displayed in A1, cell A5 shows the value of
A1 as zero(0).

So,
A1: displays a value of 205
(the value obtained from the source workbook via the link...
we're good here!)
But, despite the fact that I have set...
A5: =A1
I am seeing the following
A5: displays a value of zero(0)

Does anyone know why A5 would display zero when it should be 205? I
need A5 to reflect the numeric value of A1. I know that one possible
solution is to copy A1 and do Paste_Special-Values to cell A5. This
would be fine if the link were going to be updated once in a while.
However, the link (A1) will be updated several hundred times per day!
Because of this, manually performing a Copy then Paste_Special-Values
operation every time the value in A1 changes is unrealistic.

Is there a simple way to get A5 to automatically (i.e., no
intervention from me) reflect the new value in A1? If this will
require VBA, I would appreciate any code that you may be able to
provide as I am far from a VBA guru.

Thanks. This would be a HUGE help!!

Jax wrote:
I am seeing the following
A5: displays a value of zero(0)


In the status bar are you seeing "Circular" or "Calculate"?

If there is a circular reference in your worksheet then some formulas
may not be updated when the sheet is recalculated.

If you are not in automatic calculation mode (Tools / Options /
Calculation / Automatic) then the formula will not automatically update
when the data in A1 changes.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

I don't think your formula is actually in a cell. I believe it is sitting in
an OLE Object/link above the cell. Select it and delete in.

With both workbooks open in the same instance of Excel,
In A1 put in a formula

=[Source.xls]Sheet1!A2

--
Regards,
Tom Ogilvy


Jax wrote in message
om...
Hello,


I am having a problem with a link in one of my Excel worksheets and
I'm hoping that someone can help me with it. Here's the situation...

I have two separate workbooks that are connected via a link such that
an update in one workbook is automatically (and immediately) reflected
in the second book. There is a cell (say A1) in the second book (the
destination) that contains a link with the following format:

=Excel.Sheet.8|'C:\Folder\Source.xls'!'!Sheet1!R2C 1'

[I created this link by copying the source cell from the source
workbook and pasting (using Paste_Special) in the destination cell in
the destination workbook.]

NOW...this part works great! No problems at this stage. An update in
the Source workbook is reflected in the Destination workbook just as I
want it.

BUT...here's the problem. I have a cell, A5, in the destination
workbook that is supposed to perform a calculation based on the value
in cell A1 (the cell containing the link). Unfortunately, even though
there is a numeric value displayed in A1, cell A5 shows the value of
A1 as zero(0).

So,
A1: displays a value of 205
(the value obtained from the source workbook via the link...
we're good here!)
But, despite the fact that I have set...
A5: =A1
I am seeing the following
A5: displays a value of zero(0)

Does anyone know why A5 would display zero when it should be 205? I
need A5 to reflect the numeric value of A1. I know that one possible
solution is to copy A1 and do Paste_Special-Values to cell A5. This
would be fine if the link were going to be updated once in a while.
However, the link (A1) will be updated several hundred times per day!
Because of this, manually performing a Copy then Paste_Special-Values
operation every time the value in A1 changes is unrealistic.

Is there a simple way to get A5 to automatically (i.e., no
intervention from me) reflect the new value in A1? If this will
require VBA, I would appreciate any code that you may be able to
provide as I am far from a VBA guru.

Thanks. This would be a HUGE help!!

Tom Ogilvy wrote:
I don't think your formula is actually in a cell. I believe it is sitting in
an OLE Object/link above the cell. Select it and delete in.


Well spotted, Tom.
I didn't think of that explanation for the curious link formula.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Well, first, thanks to all that responded!

Second, although the suggestions that were given were great, I still
have one "basic" problem (I stupidly failed to mention this next part
before). Here's the question:

What if I want to link several workbooks together, with each workbook
sitting on a different computer? For example, say workbook1, sitting
on computer A, is linked to workbook2, on computer B, via shared
network drive P. Workbook2 is linked to workbook3, on computer C, via
the same shared drive P. The computers can "see" each other and book2
(on the shared drive) is simply acting as a middleman shuffling data
between book1 (on computer A) and book3 (on computer C). I know it's
a weird scenrio but I have to work with it (unless someone has any
suggestions)...

This was the original reason that I used the copy and paste method for
linking the workbooks. Unfortunately, that method won't allow me to
perform any calculations on the linked data (as we already know).

Any suggestions on how to get this scenario to work?

And thanks again for your help!!!


-J

wrote in message ...
I think the problem is the way you are linking to A1 in
workbook 1. Perhaps I am wrong. If so, my apologies for
what I suggest below,

You say you have "Copied" and "Pasted" the value of Sheet
1, R2C1 in Book 1 into A1 in book 2. Then you set A5 in
book 2 to use A1 in book 2 to perform a calculation, eg
(Value in A1 x 2).

If book 1 Sheet1 R2C1 held 10, and the formula in book 2
A5 held A1 x 2, A5 should show 20.

I don't see that Copy and Paste will work. It will only
copy the current value of book 1 sheet 1 R2C1 into book 2
cell A1. So, if at some stage the cell in book 1 Sheet 1
R2C1 gets changed, A1 in book 2 will not be updated.

What I mean is, when you copied and pasted, if the
current value in book 1 Sheet 1 R2C1 was 0, then A1 in
book 2 will always be 0.

You need to create a dynamic link. Do this;

1] Open both workbooks in Excel. Don't maximise either,
have them both so that one overlaps the other but you can
click on either to make it active at any stage.

2] In book 2, make sure cell A1 is empty, then click
inside it. Press = (the equal key on your keyboard).

3] Make book 1 active by clicking the header bar.

4] In Sheet 1, click cell R2C1. Then click enter. That
will finish the action for cell A1 in book 2.

5] In book to empty cell A5 and click = again.

6] Click A1, then enter in the formula bar any other
calculation you have to enter, eg =A1*65.345

7] Press enter and cell A5 should then "always" show the
calculation.

Jax wrote:
For example, say workbook1, sitting
on computer A, is linked to workbook2, on computer B, via shared
network drive P.


Not sure I understand what you mean by "via shared network drive P"

A workbook can be stored on a network drive and may be opened on one or
more computers. If Workbook2 is stored on drive P and is the source of
a link in Workbook1 then the fact that workbook2 is open on computer A
is irrelevant as far as the link from workbook1 is concerned.
Workbook1 just "sees" the information in the saved version of workbook2
on drive P.
The link will be updated (if the user so requests) when workbook1 is
opened and when the user requests by Edit / Links / Update.

Excel has a (flawed) concept of shared workbooks, whereby multiple
users can simultaneously update a workbook. A user only sees changes
previously saved by other users when he/she saves his version. I don't
recommend using shared workbooks.

The other option, which I would recommend, is to use a database table
to store data which needs to be shared and updated by multiple users on
a network. Databases were designed to handle updates from multiple
simultaneous users; spreadsheets weren't.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup