Formula that skips rows when Copy Pasted

[ykr445]

In the "stats" tabs, I am trying to add info to each row for each employee. In the "Document" Tab I want the info from "Stats" Tab to populate but when I copy the formula down I will skip about 33 lines in the actual sheet.


For Example:
"Stats" A2= "document" C2
"Stats" A3= "document" C15
"Stats" A4= "document" C28


And Also A similar one but for a different Column
"Stats" B2= "document" C4
"Stats" B3= "document" C17
"Stats" B4= "document" C30


There will be other rows and columns but the same concept. If I can see the proper Formula for those two I am sure I can alter it for the rest.


Please let me know what the Best formula is for this. Thank you!

 

[Takae]

Stats A3 = "=OFFSET(document!$C$2,(ROW()-ROW(A$2))*13,0)"
Satas B3 = "=OFFSET(document!$C$4,(ROW()-ROW(B$2))*13,0)"

 

[Peter_SSs]

In the "Document" Tab I want the info from "Stats" Tab

My interpretation of that is to put this formula in 'Document' C2 (adjust the 'stats' tab range as required)
INDEX(stats!A$2:A$20,(ROWS(C$2:C2)+12)/13)
You could** then Copy/Paste this to C15, C28 etc

In C4 of 'Document:
=INDEX(stats!B$2:B$20,(ROWS(C$4:C4)+12)/13)
Copy to C17, C30 etc

** Providing there is nothing in C3, C16 etc, you could enter both the above formulas then Copy C2:C4 and Paste to C15, C28 etc


Also, depending on what else you might be having in column C of 'Document', you might be able to put something like this in cell C2 and just copy it down the column.

=CHOOSE(MIN(MOD(ROWS(C$2:C2)+12,13)+1,4),INDEX(stats!A$2:A$20,(ROWS(C$2:C2)+12)/13),"",INDEX(stats!B$2:B$20,(ROWS(C$2:C2)+10)/13),"")

 

[ykr445]

Hey Peter_SSs, Thank you for the formula. It did work. If I wanted to change it so that I copy paste it every 33 rows how would the formula change? For example, the First row is C6 then again in C39. The "stats" portion would stay the sa

 

[Peter_SSs]

Hey Peter_SSs, Thank you for the formula. It did work. If I wanted to change it ..

Which formula(s)? I provided a few.

 

[MAP77]

Hi I've got kind of a similar query but cant seem to adapt the above suggested formula to my sheet.
Sheet 2 has a merge of 3 rows in column C. I want that cell to equal column AD in Sheet 1. I want to them copy the formula down in Sheet 2 so that it picks up every row in AD Sheet 1 but when I try to do this Sheet 2 copies the formula in the 2 rows of the merged cell. So it needs to skip 2 rows.
EG.
Sheet 1 AD1 = Sheet 2 C1,2,3 (as a merged cell)
Sheet 1 AD2 = Sheet 2 C4,5,6 (as a merged cell)

Thanks so much

 

[Peter_SSs]

Try this. In the merged C1:C3 of Sheet2, put this formula (adjust the '100' if required)

=INDEX(Sheet1!AD$1:AD$100,(ROWS(C$1:C1)+2)/3)

Now drag it down to the other merged cells.