How to copy every n rows from 3 sheet to 1 sheet

[rezalearner]

Hi. I have a worksheet wich has 4 sheets.
I want to copy values in every n rows in 3 sheets to one sheet. Maybe i cannot explain my problem good but as i attached the worksheet you can easily see what the problem is. I want to know what formula should i use??? (Please see my worksheet) thanks

[Kirkules]

Greetings!

Not sure if this helps, I look at this a bit differently. I'd consider rearranging your data by type. I did a lookup across the sheets. (Example attached)

Please Login or Register  to view this content.

G2 = student number
Math!$A$2:$B$7 is where it will look for G2.
When it finds it, it'll return the column 2.

[Gregb11]

Two Options:
In C1 enter:
=INDEX(Math!$B$1:$B$5,MATCH('Ma-Ph-Ch'!B1,Math!$A$1:$A$5,0))
C2:
=INDEX(Physics!$B$1:$B$5,MATCH('Ma-Ph-Ch'!B2,Physics!$A$1:$A$5,0))
C3:
=INDEX(Chemistry!$B$1:$B$5,MATCH('Ma-Ph-Ch'!B3,Chemistry!$A$1:$A$5,0))

Then select all three and drag them all down.

OR, if you have the sheet names the same as what's in Col A, you could try this in C1:
=INDEX(INDIRECT(A1&"!B$1:B$5"),MATCH('Ma-Ph-Ch'!B1,INDIRECT(A1&"!$A$1:$A$5"),0))
and copy this one formula down.

[bebo021999]

Or:

Please Login or Register  to view this content.

[rezalearner]

Two Options:
In C1 enter:
=INDEX(Math!$B$1:$B$5,MATCH('Ma-Ph-Ch'!B1,Math!$A$1:$A$5,0))
C2:
=INDEX(Physics!$B$1:$B$5,MATCH('Ma-Ph-Ch'!B2,Physics!$A$1:$A$5,0))
C3:
=INDEX(Chemistry!$B$1:$B$5,MATCH('Ma-Ph-Ch'!B3,Chemistry!$A$1:$A$5,0))

Then select all three and drag them all down.

OR, if you have the sheet names the same as what's in Col A, you could try this in C1:
=INDEX(INDIRECT(A1&"!B$1:B$5"),MATCH('Ma-Ph-Ch'!B1,INDIRECT(A1&"!$A$1:$A$5"),0))
and copy this one formula down.

Or:

Please Login or Register  to view this content.

thank you all . blue one woked best.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.