Is there a way within an Excel 2010 workbook to link data stored in a specific tab of an external workbook, and have it update automatically in the same way as you can with data imported from CSV, Access, or other data sources?

I am trying to create an Excel file that analyzes data that is stored in a number of external Excel files that are maintained by hand and stored on a shared drive. I'd like my spreadsheet to work off of the data in those files, including basic structural changes (changed cell values, changed column header names, adding or removing rows). I can make this work with data CSV files, and Excel 2010 has enough options to do the right thing with that linked data (e.g. new rows in the source are reflected as new rows in my workbook; deleted rows are similarly updated; optionally update the data when the workbook is opened; etc).

Any suggestions on how to do this with workbooks? The source data is in several, multi-tab workbooks that have formatting in them, so it is impractical to ask the workbook owners to switch to editing and maintaining CSVs. Paste As Link won’t work for a number of reasons, for example new rows added to the source don’t show up automatically in my worksheet.

I'm driving myself nuts trying to figure this out – it just seems like it should work, but there is no “From Workbook” option under Data > Get External Data. Do I have to do something extreme like converting those source workbooks to ADO data sources or some such??

  • did you find a solution ? if yes, it would be nice to share it here by replying your own question! I have the same issue, that was easy to solve with XL 2003, but now I am a bit lost. – Patrick Honorez Oct 27 '14 at 11:37

I have never done this before, but this link seems to be what you are looking for. While it is not as easy as doing it for all of those other data sources, it can be done.

All that needs to be done is enclose the path to the workbook in single quotes, and then append the standard structure for getting specific sheet from that workbook.

Example: =Sum('c:\excel_files\[test.xls]sheet1'!a1:a100)

Is the basic structure that it seems that you should be using. Note that these references will not update in real time, the file must be reopened or otherwise refreshed.

  • This won't work -- you are peforming an aggregate referencing data in an external spreadsheet. I need to actually import the data, in part because I am adding additional calculated columns. More generally, I don't think the above will work in the presenece of added rows. If you add row a101 to the source, it won't be referenced, correct? – Ramon Nov 30 '11 at 22:01

If the "external file" is saved in the 2010 or later format (.xlsx) you can easily link it "the old way" by using Data > From Other Sources > From XML Data Import.
Note that you will have to ask Excel to display "All files (.) instead of XML files (*.XML).
It works like a charm and produces a nicely formatted list that you can "Refresh" anytime.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.