4

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??

1
  • 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.
    – iDevlop
    Oct 27, 2014 at 11:37

2 Answers 2

2

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.

1
  • 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, 2011 at 22:01
0

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.


Edit: with XL 2016, you can easily link and paste a list you can refresh using Power pivot. In PowerPvt, use Get External Data, From other Sources. At the bottom of the list, you have Excel File.

2
  • This seems to import only 1st sheet. I was expecting to see sheet names in the pivot table fields list and be able to switch between different sheets but, alas, it's not there. How do we import / connect to multiple sheets and switch between them? Thanks
    – Confounded
    Nov 18, 2019 at 16:33
  • @Confounded: not sure, a bit far away. But I just checked with XL 2016, you can easily link and paste a list you can refresh using Power pivot. In powerPvt, use Get External Data, From other Sources. At the bottom of the list, you have Excel File.
    – iDevlop
    Nov 18, 2019 at 16:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .