MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copying groups of cells and incrementing a counter


Posted by Mohsin on September 19, 2001 12:56 PM

I have three rows in a worksheet that pick up values from another worksheet's columns.
row1 --> =mysheet!A2
row2 --> =mysheet!D2
row3 --> =mysheet!F2
I want to fill in the rest of my worksheet so that I can get something like the following.
row1 --> =mysheet!A2
row2 --> =mysheet!D2
row3 --> =mysheet!F2

row4 --> =mysheet!A3
row5 --> =mysheet!D3
row6 --> =mysheet!F3

row7 --> =mysheet!A4
row8 --> =mysheet!D4
row9 --> =mysheet!F4
....

Thanks


Posted by Josef on September 22, 2001 2:39 PM


Put this formula in A2 and fill down :-

=INDIRECT("mysheet!" & IF(MOD(ROW(),3)=0,"D",IF(MOD(ROW(),3)=1,"F","A"))&IF(MOD(ROW(),3)=0,ROW()/3+1,IF(MOD(ROW(),3)=1,ROUNDDOWN(ROW()/3,0)+1,ROUNDUP((ROW()/3)+1,0))))


Posted by Raymond on September 23, 2001 1:28 PM


A bit shorter :-

=INDIRECT("mysheet!"&IF(MOD(ROW(),3)=0,"D",IF(MOD(ROW(),3)=1,"F","A"))&ROUND((ROW()/3)+1,0))