Hi all,
I feel like what I am trying to do is something pretty simple in the Excel world and I think I am missing some basic concept that's preventing me from acheiving it.
I have about 20 identical tables for 20 different provinces to fill out. Each of these tables is in a different workbook because they are sent separately to each of the provinces. Each of these tables have the same exact columns, as we are requesting the same information from each of these provinces. Specifically, each of these 20 provinces are reporting information about the condition of their various cities. And the provinces may have a different number of cities, but the number of criteria they are reporting on is the same. So, pratically, this means that each of the Province tables have a the same number of columns, but may have a different number of rows as the number of cities is different.
I simply want a method to take Province A Table and paste it into a Master Table. Then, starting from wherever the first empty row is take data from the Province B Table and tack it on. And so on for all the 20 Provices. I don't think that, at this point, I want any sort of summing of data. I just want an exact copy in the master table. Although, the ultimate idea is create some sort of pivot chart with all of the data, so if it's necessary to do some aggregation before adding to the master table, I guess, we could to that.
Three quirks:
1. The number of cities being evaluated may change in the provinces. So, I was hoping that the Master Table could be dynamic such that if in Province A, we are suddenly adding on a new City (or, in orther words, increasing the number of rows in that Province Table), then the data from Province B would be pushed down.
2. This collection of information is something that happens weekly: every week the tables are sent out to the provinces and the next day they are sent back. They are then compiled into a master table to be analyzed. So, I don't want a method where we copy and paste it each week. I wanted a method, where they just update the info the Province Tables and Master Table is automatically changed to reflect the new information that we received that week. When I first made the Master Table, I had just manually created links from master spreadsheet to Province spreadsheet. This was fine, but then I ran into the problem of a new city being added to the Province Table.
3. This is supposed to be a spreadsheet used for people that don't know a whole lot about excel, so I was hoping that it could rely of formulas. Is it possible to do this without using VBA or recording macros?
Anyway, please let me know of any thoughts. I've done a lot of googling and am only coming across Consolidation and VBA and Excel add-ons. It seems like somethign so easy: just tacking a couple of tables together. The Table tool in Excel is already able to dynamically expand when you are adding rows to it, so I just thought you could do something in the Master spreadsheet like: Table 1 + Table 2 + Table 3...
Thanks so much!
Bookmarks