Hello,
I am using Excel 2007 and have multiple defined/named tables that all have the same field headers. I would like to create a dynamic table that combines all of these tables. I can create such a table by copying the data in each table, and pasting it in a new table ("Big Table") using the "paste special" and "paste link". But, sometimes the amount of data in the source tables changes, and then this data isn't included in the master table. I can paste in extra rows in to the master table, and just keep them empty, but this is unwieldy, and sometimes I exceed the number of blank rows I added. There also isn't any error checking if this happens, other than the user paying attention to the amount of data added.
Since Excel's tables are smart, and adjust their size as you add more data, it seems to me that I should be able to build my master table using references to the source table names, but I cannot figure out how to do it. The contents of my tables are categorical data, so the "consolidate" function does not work because it wants to perform math (you must choose a "formula") on the contents of the cells. I cannot make the pivot table work, because I loose a lot of sorting capabilities. For example, if I have column headers A, B, C and D in my tables, when I consolidate by pivot table, I now only have the field "columns" to move around, while if I created a pivot table from "Big Table", I can individually access fields A, B, C, and D, which is what I need.
A variety of plugins exist for purchase that claim to do this, but none of them use the named tables to create the master table. If I have to manually select my ranges, then I'm better off with my lame pasting special-hyperlink with 50 extra empty rows method. I've spent hours searching for a solution... this seems a pretty straight forward task, but none of the search terms I have tried (merge, join, append, concatenate) turn up anything other than the consolidate or pivot table option. I did find some solutions that use SQL queries, but they don't seem to recognize (excel's named) tables, only the sheets (when system tables are selected) and some columns. Since I have multiple tables per sheet, and narrative text as well, this did not work. Additionally, the columns that I have tables in did not even appear in the selection menu, which was a little odd.
Hope this is clear. If not, I can create a demo excel workbook and post it.
Thanks.
L.
Bookmarks