Retrieve data from several worksheet into one

[Pierre]

Hello,

I have files with data in several worksheet. Each worksheet is a day of a month (so up to 31 worksheet to retrieve data from). Data in the worksheets are in the same columns, but numbers of rows from one sheet to another can be different. I would like to be able to retrieve all the data from the different worksheet into a unique worksheet.

Is there any way to do that with formula?

I enclosed a small example

Thanks in advance!

 

[bosco_yip]

Try,

In C2, copied right to D2, and all copied down :

=IF(ROWS($1:1)<=COUNT('1'!$A$1:$A$30),INDEX('1'!A$2:A$30,ROW(A1)),IF(ROWS($1:1)<=COUNT('1'!$A$1:$A$30)+COUNT('2'!$A$1:$A$30),INDEX('2'!A$2:A$30,ROWS($1:1)-COUNT('1'!$A$1:$A$30)),IF(ROWS($1:1)<=COUNT('1'!$A$1:$A$30)+COUNT('2'!$A$1:$A$30)+COUNT('3'!$A$1:$A$30),INDEX('3'!A$2:A$30,ROWS($1:1)-COUNT('1'!$A$1:$A$30)-COUNT('2'!$A$1:$A$30)),"")))

Regards
Bosco

 

[Pierre]

Thanks bosco_yip!