Update links

[Bill Ridgeway]

I have a spreadsheet which, until recently, on opening updated links to
external spreadsheets (whether or not the file was already open). Now, for
some strange reason, it updates all but one link but it will do so when that
file is already open. I get the message "The workbook contains one or more
links that cannot be updated".

Any ideas why this should happen please?

Thanks.

Bill Ridgeway

 

[Dave Peterson]

Any chance that the link points to a CSV file--not a normal excel workbook?

If not, you may want to share the formula that contains the link.

 

[Bill Ridgeway]

Thanks Dave,

The link is to another worksheet. The link was made some while a go and has
now 'failed'. I have tried to re-establish the link simply by typing "="
and then navigating to the cell in the target worksheet. I'm not aware of
any changes that may have caused this "it worked OK yesterday" problem.

Regards.

Bill Ridgeway
Computer Solutions

 

[Dave Peterson]

A worksheet in the same workbook????

(You did check to see if calculation was set to automatic, right?)

I've never seen this, but others have posted that their formulas won't update
even when calculation is set to automatic.

One solution that seems to work is to group all the worksheets and then select a
single cell (so all the cells are affected).

Then edit|replace
what: = (equal sign)
with: =
replace all
and ungroup the sheets

 

[stevenrjohnson]

A worksheet in the same workbook????

(You did check to see if calculation was set to automatic, right?)

I've never seen this, but others have posted that their formulas won't update
even when calculation is set to automatic.

One solution that seems to work is to group all the worksheets and then select a
single cell (so all the cells are affected).

Then edit|replace
what: = (equal sign)
with: =
replace all
and ungroup the sheets









--

Dave Peterson- Hide quoted text -

- Show quoted text -

I am experiencing a similar situation. The problem was that links to
an external or another workbook were failing and in turn crashing
Excel 2000. On Excel 2002/XP is showed me that some links were fine
and some errored. The files it was linked to where downloaded and
save from the internet.

The solution for my problem was to open the source files that were
downloaded and save them in excel. After doing that I could see that
link come back to an "OK" status which had shown error before. Now I
may have to write a macro to open, save and close each of these
files. It is the only solution that I know at this point.

Can anyone think of a better way to do this? I have another macro
that creates a series of excel files that are separate reports for
every state and country... it is weather data. When you open one of
these files and then close it, it prompts you to save the file. Why
is that? Was the file not just opened up? Any ideas here?

Thanks,

Steven Johnson
Desktop Support/Programmer
A.A.S., A+, i-Net+, CIW

 

[Dave Peterson]

Just the last portion...

There are some worksheet functions that are volatile--that are recalculated when
excel recalcs.

AREAS()
CELL()
INDIRECT()
ROWS()
COLUMNS()
NOW()
TODAY()
RAND()

Any of these functions in formulas could make excel think your workbook has
changed.

===
As for the first portion, what kind of error did you see--like #ref's?

Maybe a little more info will help others come up with a suggestion that
works????



:

 

[Guest]

There are two possibillities:

1. Possibily you may be linking to a comma seperated values (csv).

Note: Although the dialog box diaplays the following message:

..csv file may contains features that are not compatible with csv (Comma
Delimited). Do you want to keep the Workbook format.

Click Yes. You view the .csv file in the Excel iteself. The solution is open
the .csv file and copy and paste special -> paste link into the target file
and the updation is simultaneous when both the files are open.

2. Check whether the path of the linked file has not changed.
3. Check whether you have not renamed the named range name.


Challa Prabhu

 

[Bill Ridgeway]

Thanks for all your suggestions. I have cracked this problem in a totally
different and, I think, a simpler way.

Having renamed my problem file, I opened a new file, inserted and renamed
(from the default) new worksheets, copied all data from worksheets on the
old file to worksheets on the new file, amended formulas with references to
external spreadsheets.

I would like to know how the heck this problem occurred so, if possible, I
don't do 'that' again but I suppose I should be happy that the problem has
been resolved.

Regards.

Bill Ridgeway
Computer Solutions