Hello XLS S; Let me try to explain my best :)
In sheet2 B1, used a formula to get all items in sheet1, which is =COUNTA(Source!A3:Z65536) B2, used too get total columns used, which is =COUNTA(Source!A2:Z2) Based on the attached, there are 3 columns & 300 rows. So, first 3 rows need the data from row 1 in sheet1, but different columns. Next 3 rows from Row2 Row1_ColA Row1_ColB Row1_ColC Row2_ColA Row2_ColB Row2_ColC So, the logic is after 3 rows Row # needs to increase 1, Column # needs to add 1 on each row, after 3 rows it needs to reset to 1 INT will rounddown a value to Integral. say, 2.99999 will round to 2, 0.0001 to 0, 3.5 to 3 etc.... A5 formula =INDEX(Source!$A$3:$Z$65536,INT((ROWS(A$5:A5)-1)/$B$2)+1,MOD(ROWS(A$5:A5)-1,$B$2)+1) (ROWS($A5:A5)-1) *| *How may rows in A5:A5 = 1, 1-1 = 0 (ROWS($A5:A5)-1)/$B$2 *|* 0/3 = 0 INT((ROWS(A$5:A5)-1)/$B$2)+1 *|* INT(0)+1, 0+1 = 1 MOD function will returns the remainder after number is divided by divisor. eg: =MOD(3,6), multiplication of 6 where falls <=3, here it returns 1. If number is less than the divisor, MOD will return the same number. =MOD(20,6), multiplication of 6 where falls <=20, here it falls on 18 (3*6), So the remaining value is 2. MOD will return 2 =MOD(25,5) multiplication of 5 where falls <=25, here it falls on 25 (5*5), So the remaining value is 0. MOD will return 0 In the attached, MOD(ROWS(A$5:A5)-1,$B$2)+1 ROWS($A5:A5)-1 *| *How may rows in A5:A5 = 1, 1-1 = 0 MOD(0,3)+1 *|* 0+1 = 1 So the INDEX will become, INDEX(Source!$A$3:$Z$65536,1,1) A15, formula will be like =INDEX(Source!$A$3:$Z$65536,INT((ROWS(A$5:A15)-1)/$B$2)+1,MOD(ROWS(A$5:A15)-1,$B$2)+1) INT((ROWS(A$5:A15)-1)/$B$2)+1 *|* INT(10/3)+1, INT(3.33333333333333)+1, INT(3)+1, 3+1 = 4 MOD(ROWS(A$5:A15)-1,$B$2)+1 *|* MOD(10,3)+1, MOD(1)+1, 1+1 = 2 So the INDEX will become, INDEX(Source!$A$3:$Z$65536,4,2) If you use Formula Evaluator tool, you can see the calculation easily step by step than my longest explanation :) HTH Haseeb -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com