Link 2 Excel files

[Salman]

If I link two files – save and close both then just open 1 of those, it
doesn’t pick up the links/formulas and shows ‘#VALUE!’ for each cell, until
the other one is open too.
I manually linked them ie open 2 files; in one cell type ‘=’ then go to the
other file and link accordingly – or – vlookup, sumif etc to another excel
file.

On the same note – sometimes I link the entire file or tab from the same
file (ie ctrl+K then select the file or tab) but often that also fails to
reopen.

First I thought its because I am linking different Excel files ie ‘Excel
97-2003’ with xls (Excel 2007). Or xls with macro to xls without macro etc.
But even I tried linking the same files eg xlsm with xlsm – still the same
problem.
If I open the source file, it corrects the formula in the other file but if
I only open the linked file (while source file is not opened) – it shows
‘#VALUE!’.
Is there any way to get rid of this error without even opening the source
file otherwise I will have to open and close alot of files before I start
work on any Excel file as most of my files are linked to many other files. At
present its taking alot of time to open all other files, save and then close
those to get rid of ‘#VALUE!’ errors. I was wondering if there is any other
way to get rid of these errors.

For future reference - do you reckon linking different excel files could
have caused this problem?

Thanks in advance

 

[Normek]

Have you tried:
In Excel 2007: Office Button - Excel Options - Advanced - Updatelinks to
other documents

In the other versions of Excel it will probably be under the File menu or
Tools menu.

 

[Salman]

Yes - 'Updatelinks to other documents' is ticked

Have you tried:
In Excel 2007: Office Button - Excel Options - Advanced - Updatelinks to
other documents

In the other versions of Excel it will probably be under the File menu or
Tools menu.

 

[Salman]

Yes - 'Updatelinks to other documents' is ticked

Have you tried:
In Excel 2007: Office Button - Excel Options - Advanced - Updatelinks to
other documents

In the other versions of Excel it will probably be under the File menu or
Tools menu.

 

[Normek]

I think you have to use Copy then Paste Link to get it to automatically update.

 

[Salman]

Hi Normek. Thanks for the reply.
Its showing an error message that Excel cant complete this task with
available resources. Then showing ‘unable to paste link’

Even if this had worked, do I have to use this for each file I will link to
other files?
Does it matter if the files are password protected?

 

[Normek]

You do need to unprotect your worksheets/workbooks to Paste Link. But once
the link is established it will update even if the cell is locked and the
worksheet and/or worksheet is protected.
Yes each Link in another workbook has to be established seperately, unless
the source cell is accessed multiple times, in which case you can use the =
operator to a linked cell in the target workbook.
Lookup and sumif formulas are a bit more difficult. Try building them in a
textbox and pasting them into Formula bar.