Dynamic merge of tables

**chrisanouk** · 12-09-2011, 10:35 AM

Hi all,
I have a workbook containing 3 worksheets. Each worksheet contains a table. The column headers of the tables are consistent across all 3 worksheets. Basically, I want to add a 4th worksheet which dynamically adds each existing and new line from the 3 separate tables.
The table has 8 column headers with many many rows of data entered. They are:

Fiscal Year
Fiscal Month
Fiscal Week
Week Beginning
Store name
Product Cat
Product
Sales Units

The only item that can be 'summed' is the sales units column, so the Data>Consolidate feature does not work for me and I receive some very strange results.

Any ideas? I have searched everywhere. I don't really want to use a separate excel add-in as I am creating this centrally for use in countries where the add-in will not be available.

Thank you in advance for your help!!!

**tigeravatar** · 12-09-2011, 02:31 PM

chrisanouk,

Attached is an example workbook based on the criteria provided. Sheet4 is named 'All Data' and it has the headers of the other sheets. In Cell A2 is this formula:

Please Login or Register  to view this content.

And then it is copied over for each column with a header and down until row 101 (which works for this example because the total amount of rows in sheets 1, 2, and 3 is only 60). The extra rows are blank, the rows that should have data, do have data which is pulled from the three different worksheets in order.

In sheets 1, 2, and three, column Z is this formula in cell Z2 (this formula gets the sheetname of the sheet the formula is in):

Please Login or Register  to view this content.

In sheet 'Add Data' column Y is this formula in cell Y2 and then copied down (this formula collects the worksheet names of the different sheets. This is done to allow the sheets to be renamed and it will not affect the final result):

Please Login or Register  to view this content.

Finally, an Index/Match table is setup in 'All Data' column Z which counts the number of used rows in each worksheet. Cell Z1 = 0. Cell Z2 and copied down is:

Please Login or Register  to view this content.

These supplemental formulas in columns Y and Z of each worksheet are hidden so that the user cannot see them. Is something like that what you're looking for?

NOTE: To accomplish its goals, the formula uses Indirect and Offset, both of which are volatile functions, so with a large workbook, it could bog things down and wouldn't necessarily be a good solution.