Creation of a Master Table

[trg]

Hello,

I have 17 tables all on different sheets. They all have 4 columns each and have the same column names each. Each table is named differently. I want to create a master table that contains all of the data of these smaller tables.

Small Example.


Table 1
Column1 | Column2 | Column3 | Column4
1 | 1 | 1 | 1


Table 2
Column1 | Column2 | Column3 | Column4
2 | 2 | 2 | 2





Creates the following table.




Master Table
Table 3
Column1 | Column2 | Column3 | Column4
1 | 1 | 1 | 1
2 | 2 | 2 | 2

[abdulji]

Create a new sheet. Use the consolidate option.
Select the values you need in the master table
Select Create links to source table if you want the master table to reflect any changes made in those tables

[gerotutu]

maybe this helps you http://www.mrexcel.com/forum/excel-q...ml#post4230690

Cheers,
Gerónimo

[newdoverman]

This may work for you. I created tables on Sheet2 and Sheet3 and on Sheet1 I have them combined.
There are several formulae involved to number the rows on each worksheet consecutively.
Eg. Sheet2!A2 this is used to produce consecutive numbers when there is data in column Sheet2 column B.

Please Login or Register  to view this content.

A1 of the worksheets that follow take the max value from column A of the previous worksheet.
Sheet3!A1

Please Login or Register  to view this content.

Please Login or Register  to view this content.

A small table is created to show the worksheets and the max value of column A of each worksheet.

	Q	R
1		0
2	Sheet2	=MAX(INDIRECT("'"&Q2&"'!a:a"))
3	Sheet3	=MAX(INDIRECT("'"&Q3&"'!a:a"))

Sheet1!A2 has the following formula which is filled down column A and this copies the worksheet names the appropriate number of times according to the max number. Sheet2 is easy as it is the max of A:A but Sheet 3 number of rows is the max number - the max of the previous worksheet. This formula takes that into consideration with the MATCH function.

Please Login or Register  to view this content.

With the sheet names now in column A of Sheet1, the values for each worksheet need to be retrieved. I have used VLOOKUP that makes use of the sheet name in column A.

The VLOOKUP value is determined by the ROWS function that starts counting up from 1 with the argument ($1:1). The table array being addressed is established by the value in A2 and the range A2:E15. The INDIRECT function takes "'"&$A2&"'!a2:e15" and makes it a useable range that Excel understands as the Sheet name and cell range. The COLUMNS function is another counter for the column being returned. $A$1:B1 is 2 and as the formula is filled across, this value changes to $A$1:C1 and $A$1:D1 etc. to return the values from the incremented column.