|OK, this seems to work, but you're going to have to figure out the correct syntax based on your Sheet Names, etc. I did this by using data and tables all from the same sheet to make it easier to explain.|
I started with a set of values in Column A, and added a "Group Number" for each value in Column B. (You can place this Group Number column anywhere you want, just alter the VLOOKUP portion of the final formula to match.)
1 1 1
2 2 1
3 3 1
4 4 1
5 A 2
6 B 2
7 C 2
8 $ 3
9 # 3
10 & 3
11 @ 3
I then selected the upper left cell of each "table" and Named each cell as follows:
For example, for your early example of the table in A29:L53, cell A29 would be named Table1.
If your next table was in A54:L106, A54 would be named Table2
Now, in I16 (the first cell where the tables should be imported to) I placed this formula:
I then dragged it across the width of the table and then down the length of the table.
How this works is as follows:
VLOOKUP will grab the Group Number from Column B based on the value in C1.
We then concatenate the word Table and the Group Number to return Table1, Table2, etc. which match your Range Names.
The INDIRECT function will reference the Named Range which is the first cell in each table.
The OFFSET function will use the COLUMN and ROW calculations to determine how many Columns and Rows to OFFSET from the Named Range, which is the upper left cell of each table.
In other words, ROW()-16, COLUMN()-9 in I16 returns an offset of 0, 0. As you drag the formula across and down, those numbers will increment accordingly.
So, for example, with a 2 in C1, the formula in I16 can be reduced to:
which is the value in A29.
In I17, it can be reduced to:
which is the value in A30.
And so on and so on.