Please help me for 1 Formula

[aparvez007]

Hi All,


I have 2 sheet in a same file sheet1 contain all customer no. & Customer Name.

i.e. B1:C80 Customer No. & Customer Name

and sheet2 have some format like Customer No. in B6 and customer Name in C6 then 6 blank row then B13 Customer No. C12 Customer name which i want from sheet1.


hope I explain my problem well.


Regards,

Pavi

 

[kaushik03]

Hi Pavi,


I do not completely understand your requirement.


Could you plz clarify the following?


1)Do you want to fetch the data from sheet1 and paste / place it in sheet 2 every 6 rows interval?

2) is there any specific criteria to pick the data from sheet1?


However, could you plz upload a sample workbook for our better understanding of your data layout?


Kaushik

 

[aparvez007]

Thanks Kaushik,

Actually I am in office and unable to upload the file.


1. yes i want to paste the data but there are lots of customer so i want formula for that.

2. see there is no criteria just simple B1 and C1 from sheet1 will paste in B1 and C1 in sheet 2 then B2 and C2 from sheet1 will paste in B9 and C9 and soo on...


Hope I explain as per your understanding.


Regards,

Pavi

 

[Faseeh]

Hi aparvez007,


With your data in Sheet1, B1:C80 & Enter this formula and drag down and to right:

[pre]

Col-B   Col-C
Row1	B1	C1
Row2	B2	C2
Row3	B3	C3
Row4	B4	C4
Row5	B5	C5
Row6	B6	C6
Row7	B7	C7
Row8	B8	C8
Row9	B9	C9
Row10	B10	C10
Row11	B11	C11
Row12	B12	C12
Result:

[code]Col-B   Col-C
Row1	B1	C1
Row2
Row3
Row4
Row5
Row6
Row7
Row8
Row9	B2	C2
Row10

[/pre]
Formula:

=IF((ROW(A1)+7)/8-INT((ROW(A1)+7)/8)<>0,"",OFFSET(Sheet1!B$1,ROUND((ROW(A1)+7)/8,0)-1,0))[/code]


Hope that helps,


Faseeh

 

[aparvez007]

Thanks Faseeh,


I made mistake in my question.


below formla working fine.

=IF((ROW(A1)+6)/7-INT((ROW(A1)+6)/7)<>0,"",OFFSET(Dealer!D$6,ROUND((ROW(A1)+6)/7,0)-1,0))


but want to tell you onething you are great and I am dumpo didn't understand anything how formula works...


Thaks a ton again dear.


Regards,

Pavi