Combining content from multiple sheets into a single sheet

[mechanical13]

Hi Everyone,

I was hoping someone could help with Excel task I have been struggling with the past few days.

Here is the setup:

1) I have a 8 worksheets in one excel workbook that contain 15 different columns each. The worksheets are identical, but contain different data. (They are from data collection templates from different parts of the organization).

2) The number of rows on each of the worksheets varies, and will be updated each month. A few may have 10, and a few others may have 100.

What I would like to do:

1)I would like to have one worksheet that combines all the data in the 8 different sheets into a single sheet

2) Data from sheet 2 should be below content from sheet 1, content from sheet 3 below sheet 2 etc.

3) To keep it simple, I don't want to use a Macro of any sort. Just want to use formulas.

4) The sheet should update dynamically when any of the 8 individual sheets are updated. For example, if I add 10 new rows to sheet 1, I want sheet 2's data in the combined sheet to automatically shift downwards.

Functionally, I know that I'll have to find a way to find which row is the last row in each sheet, then use some form of INDIRECT addressing to do this, but I'm getting really confused.

Any help would be great help. I'm happy to send an example of the 8 individual tabs if thats helpful!

Thanks!

Janak

 

[My Aswer Is This]

I think you will need to do this with Macros but then maybe someone else will have a better answer for you.

 

[hiker95]

mechanical13,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Screenshots would probably be out of the question.

We would have to see your actual workbook/worksheets to be able to create a macro solution, or, a solution using formulae.


You can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.