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
Get External Data. Do I have to do something extreme like converting those source workbooks to ADO data sources or some such??