• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamically combining rows from different sheets into 1 sheet


New Member
I have data of diff regions: east, west, north & south

The data is in 4 diff sheets named: east, west, north & south.

The column heads are identical & formatting as well.

I want to consolidate the data from these 4 sheets into a fifth sheet of the workbook named:" Overall"

The "Overall" sheet should have the consolidated data in the sequence:

1. West

2. East

3. North

4. South

But all this should happen dynamically,

i.e. as & when i add a row of data to East sheet the consolidated data should automatically show added entry in a new row inserted below last row of east data.

Also the serial no. should adjust automatically sequentially


Sheet West:

1. CompanyA Rose 12cartons 6pcs

2. CompanyB Lotus 15cartons 9pcs

3. CompanyC Sunflower 18cartons 5pcs

Sheet East:

1. CompanyP Orange 72cartons 3pcs

2. CompanyQ Mango 55cartons 4pcs

3. CompanyR Apple 48cartons 1pcs

4. CompanyS Orange 89cartons 9pcs

Sheet North:

1. CompanyJ Shirt 20cartons 4pcs

2. Companyk trouser 95cartons 3pcs

Sheet South:

1. Company M Toy 56cartons 5pcs

Sheet consolidated:

1. CompanyA Rose 12cartons 6pcs

2. CompanyB Lotus 15cartons 9pcs

3. CompanyC Sunflower 18cartons 5pcs

4. CompanyP Orange 72cartons 3pcs

5. CompanyQ Mango 55cartons 4pcs

6. CompanyR Apple 48cartons 1pcs

7. CompanyS Orange 89cartons 9pcs

8. CompanyJ Shirt 20cartons 4pcs

9. Companyk trouser 95cartons 3pcs

10. Company M Toy 56cartons 5pcs

Observe that the serial no. is 1 to 10 & not 1,2,3,1,2,3,4,1,2,1

Kindly help.


Excel Ninja
Staff member

This is going to be interesting to explain:

On a new sheet "Total", Put a heading Row:

Serial	Company	Flower	No	Size

Make the following named ranges:

[pre][code]East	=OFFSET(East!$A$1,1,0,COUNTA(East!$A:$A)-1,COUNTA(East!$1:$1))
NOC	=COUNTA(OFFSET(Total!$A$1,0,0,1,COUNTA(Total!$1:$1)))
North	=OFFSET(North!$A$1,1,0,COUNTA(North!$A:$A)-1,COUNTA(North!$1:$1))
South	=OFFSET(South!$A$1,1,0,COUNTA(South!$A:$A)-1,COUNTA(South!$1:$1))
Total	=COUNTA(East,West,North,South)/NOC
West	=OFFSET(West!$A$1,1,0,COUNTA(West!$A:$A)-1,COUNTA(West!$1:$1))
Now on the Total Page

A2: =IF(ROW()>COUNTA(East,West,North,South)/NOC+1,"",ROW()-1)
B2: =IF(ROW()>Total+1,"",IF(ROW()-ROW($A$1)<=(COUNTA(West)/NOC),INDEX(West,ROW()-1,COLUMN()),IF(ROW()-ROW($A$1)-(COUNTA(West)/NOC)<=(COUNTA(East)/NOC),INDEX(East,ROW()-(COUNTA(West)/NOC)-1,COLUMN()),IF(ROW()-ROW($A$1)-(COUNTA(East,West)/NOC)<=(COUNTA(North)/NOC),INDEX(North,ROW()-(COUNTA(East,West)/NOC)-1,COLUMN()),INDEX(South,ROW()-COUNTA(East,West,North)/NOC-1,COLUMN())))))[/code][/pre]
Copy A2 down

Copy B2 Across and Down


Be careful that the " are correct, retype them if you get errors

Have a look at the above in action at:



New Member
I looked at your template and the "total" sheet does not have the companies consolidated in the sequence he gave with West being first as he asked. They are actually sorted strictly by the worksheet order for the companies...


Excel Ninja
Staff member

Thanx for that pick up

I have adjusted the post above and the uploaded a revised link to a new file

Never heard from Amollogs and so don't know if it was any use or not. I hope it is of some use to you