Some Excel links don't work
 

I have a workbook with many links, around two thousand, and a number of them
only work when the source file is open. This is not the case of my using of
Indirect() on some of them making them not work because all of my links are
the same, along the format of ='Y:\*Path*\*[File name]**Worksheet Name*'$J$45
..

For example, half of the data in the second week of calculations does not
update, but the data in evey other week works perfectly. This update problem
is rectified if the "Week Two.xls" file is open. I also have a problem that
the first link to many of the weeks (but not all of them) does not work
unless the referenced workbook is open, although all of the other links to
the file are updated when the file is closed, and there is absolutely no
difference between the way the links look in Excel.

If you need more specific information I can provide it.

Ben Enfield

What does "not work" mean? That you get an error or the value returned is
incorrect? (Retrieving a value from a closed workbook has a limit of 255
characters.

If you use:
Edit|Links|update links
does the problem go away?

If you did a mass change:
edit|replace
what: =
with: =
replace all
does the problem go away?



Ben Enfield wrote:

I have a workbook with many links, around two thousand, and a number of them
only work when the source file is open. This is not the case of my using of
Indirect() on some of them making them not work because all of my links are
the same, along the format of ='Y:\*Path*\*[File name]**Worksheet Name*'$J$45
.

For example, half of the data in the second week of calculations does not
update, but the data in evey other week works perfectly. This update problem
is rectified if the "Week Two.xls" file is open. I also have a problem that
the first link to many of the weeks (but not all of them) does not work
unless the referenced workbook is open, although all of the other links to
the file are updated when the file is closed, and there is absolutely no
difference between the way the links look in Excel.

If you need more specific information I can provide it.

Ben Enfield

--

Dave Peterson

"not work" means that the link is displaying "#REF". The numbers I am
dealing with are small integers (-1000 to 1000). Using Edit, Update links
does nothing but refresh the #REF. If I retype the link in the cell then it
still gives me the same error, but if I type the link for any cell that is
currently working then it still works. All sources are evaluated as "OK" by
the Edit - links - check status function. I tried replacing all of my root
paths with the same root path, hoping to have it rethink every function, but
it doesn't work. Here is the link that doesn't work:
='Y:\spada el\Daily Meterological
Reports\Daily_M_JUL2005-JUN2006\[Met_Week04.xls]SUNDAY'!$C$39

Here are two links that do:
='Y:\spada el\Daily Meterological
Reports\Daily_M_JUL2005-JUN2006\[Met_Week04.xls]MONDAY'!$C$39
='Y:\spada el\Daily Meterological
Reports\Daily_M_JUL2005-JUN2006\[Met_Week05.xls]SUNDAY'!$C$39

I don't understand the difference between my stated links. They all seem
the same. And by the way, all of the worksheets accessed
(Met_Weekxx.xls]DayOfWeek) are the same with different numbers.

"Dave Peterson" wrote:

What does "not work" mean? That you get an error or the value returned is
incorrect? (Retrieving a value from a closed workbook has a limit of 255
characters.

If you use:
Edit|Links|update links
does the problem go away?

If you did a mass change:
edit|replace
what: =
with: =
replace all
does the problem go away?



Ben Enfield wrote:

I have a workbook with many links, around two thousand, and a number of them
only work when the source file is open. This is not the case of my using of
Indirect() on some of them making them not work because all of my links are
the same, along the format of ='Y:\*Path*\*[File name]**Worksheet Name*'$J$45
.

For example, half of the data in the second week of calculations does not
update, but the data in evey other week works perfectly. This update problem
is rectified if the "Week Two.xls" file is open. I also have a problem that
the first link to many of the weeks (but not all of them) does not work
unless the referenced workbook is open, although all of the other links to
the file are updated when the file is closed, and there is absolutely no
difference between the way the links look in Excel.

If you need more specific information I can provide it.

Ben Enfield

--

Dave Peterson

When you type that you retyped the link, do you actually retype the whole thing
or just hit F2, then enter?

(If you're retyping, maybe you're changing something????)

Did you try the edit|replace suggestion?

(Yeah, I don't have a good guess!)

Ben Enfield wrote:

"not work" means that the link is displaying "#REF". The numbers I am
dealing with are small integers (-1000 to 1000). Using Edit, Update links
does nothing but refresh the #REF. If I retype the link in the cell then it
still gives me the same error, but if I type the link for any cell that is
currently working then it still works. All sources are evaluated as "OK" by
the Edit - links - check status function. I tried replacing all of my root
paths with the same root path, hoping to have it rethink every function, but
it doesn't work. Here is the link that doesn't work:
='Y:\spada el\Daily Meterological
Reports\Daily_M_JUL2005-JUN2006\[Met_Week04.xls]SUNDAY'!$C$39

Here are two links that do:
='Y:\spada el\Daily Meterological
Reports\Daily_M_JUL2005-JUN2006\[Met_Week04.xls]MONDAY'!$C$39
='Y:\spada el\Daily Meterological
Reports\Daily_M_JUL2005-JUN2006\[Met_Week05.xls]SUNDAY'!$C$39

I don't understand the difference between my stated links. They all seem
the same. And by the way, all of the worksheets accessed
(Met_Weekxx.xls]DayOfWeek) are the same with different numbers.

"Dave Peterson" wrote:

What does "not work" mean? That you get an error or the value returned is
incorrect? (Retrieving a value from a closed workbook has a limit of 255
characters.

If you use:
Edit|Links|update links
does the problem go away?

If you did a mass change:
edit|replace
what: =
with: =
replace all
does the problem go away?



Ben Enfield wrote:

I have a workbook with many links, around two thousand, and a number of them
only work when the source file is open. This is not the case of my using of
Indirect() on some of them making them not work because all of my links are
the same, along the format of ='Y:\*Path*\*[File name]**Worksheet Name*'$J$45
.

For example, half of the data in the second week of calculations does not
update, but the data in evey other week works perfectly. This update problem
is rectified if the "Week Two.xls" file is open. I also have a problem that
the first link to many of the weeks (but not all of them) does not work
unless the referenced workbook is open, although all of the other links to
the file are updated when the file is closed, and there is absolutely no
difference between the way the links look in Excel.

If you need more specific information I can provide it.

Ben Enfield

--

Dave Peterson


--

Dave Peterson