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!!