Excel 2010 External links do not update unless source file is open

I have a master workbook with tabs to 12 different spreadsheets.  One tab for each external file.  I have created formulas to pull the info into the master and the data is consolidated.  All of the formulas work and pull the correct information in.  However when I save and close all the spreadsheets and try to only open the consolidated spreadsheet, there is no data.  In order to populate the spreadsheet, I have to open all of the individual spreadsheets.

My consolidated spreadsheet will be linked to others, so I need to have the ability to have all the sheets "chained" together and get to the data.

Under Data - Edit Links - Startup Prompt, I have the "Don't display the alert and update links"
Briad ISDirector, ISAsked:
Who is Participating?
Briad ISConnect With a Mentor Director, ISAuthor Commented:
Due to the mappings, changing the format to the .xlsx did not work.  I ended up writing a macro to take the consolidated file and saving off all the tabs and doing a copy-Paste-Special-Value to remove all formulas and links.  I am then using that file for link to the other files.

Thanks for your suggestions Wilder126, BrianIS and Rob.

This web link may help you to fix you link problem.

Don't ask whether to update links when I open any workbook, and update links automatically

1- Click the File tab, click Options, and then click the Advanced category.
2- Under When calculating this workbook, clear the Update links to other documents check box. If this check box is clear, the links are automatically updated, and no prompt is displayed.

You may found other solution on this link: Control when external references (links) are updated
Briad ISDirector, ISAuthor Commented:
i made the changes, opened all the data source spreadsheets, confirmed the data was showing in the consolidated workbook, Saved and closed.  When I re-opened, I get a quick glance at the data in the consolidated sheet and then all the data is cleared.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Looks like all sheets have to be kept opened to pull the values in your destination sheet. It's doing this to me also.

But if you want to pull the data when some other sheets are closed, you may need a driver connection like ADO. But this will be a VBA excel + an ADO.
To give you more information:
ADO stands for ActiveX Data Objects, and it is an alternative way of connecting to an Excel, Access database as an example.

You can connect by using this code as an example:
Sub Connect_to_Excel_sheet()
    Dim cn as ADODB.Connection

    Set cn = New ADODB.Connection

    With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\sheet2.xls;" & _
             "Extended Properties=Excel 8.0;"
    End With
End Sub

Open in new window

This code would create a connection and open the sheet2.xls.
I nice web site that i use for connection strings is: Microsoft Jet OLE DB 4.0 connection strings

Always helpful for any type of connections.
Rob HensonFinance AnalystCommented:
What formulas are you using for linking the data?

Some formula functions do not work when the source workbook is closed and will return an error. If your formulas include an IFERROR option then this could be resulting in TRUE and returning the value set in the IFERROR rather than the linked value.

Rob H
Briad ISDirector, ISAuthor Commented:
Wilder1626,  I will look at the connections, but I have never had to do that in the past when I have a spreadsheet referencing another workbook.

I have formulas that look like the following.

=IF(ISBLANK('\\ZTJ-FILESRV01\Shared_Data\JesseM\Monday Reporting\Blank Weekly\[101cog.xls]Sheet1'!A1),"",'\\ZTJ-FILESRV01\Shared_Data\JesseM\Monday Reporting\Blank Weekly\[101cog.xls]Sheet1'!A1)

I have a tab setup for each  "xxxCOG.XLS" for a total of about 20 different files.  As long as the cog.xls files are open, I can see the values.  I save and close all the workbooks, then open the consolidated file and no values are showing.  There are no errors, just no data.  If I go to DATA--> Open Source, all the data appears.

I have done this hundreds of times without any issues, but for some reason this time it will not work.
Rob HensonFinance AnalystCommented:
Which version of excel are you using? It could be that the newer versions don't like linking to an older xls format file.
Briad ISDirector, ISAuthor Commented:
I am using Excel 2010 linking to .xls files (Microsoft Excel 97-2003 Worksheet).  The files are created from a food costing program where the data is exported to Excel format.  The only other format that it can export to is .pdf, so this is my only option.
Rob HensonFinance AnalystCommented:
Open one of the source files and a Save As converting it to 2010 format xlsx. Then close that file and see if the link stays visible. Doing the save as with destination and source files open should change the link but might need to do it manually in the Edit Links window.

Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.