Transfer data between sheets with a pattern.

**packsand** · 09-01-2011, 02:21 PM

I have a list of data on sheet 1 that contains info sorted by type (ie. name, address, city, phone #, aDOB, etc). On sheet 2 I have a roster that lists Name, address, city, and gender, one on top of the other stacked 4 rows high. How do I create a pattern where when I copy the formula on sheet 2, it will follow the correct row order of sheet 1 without skipping data?
example:
sheet 1 column C = names D = address F = city K = DOB
sheet 2 needs to look like this

NAME
ADDRESS
CITY
DOB
NAME
ADDRESS
CITY
DOB

Whenever I use the offset function, it repeats but skips four rows every time. I could go in and type every formula by hand, but I have thousands of names and poor typing skills.
Any help would be greatly appreciated.

**martindwilson** · 09-04-2011, 03:39 PM

assuming data starts in c2
=INDEX($C$2:$F$300,FLOOR(ROW(A4)/4,1),IF(MOD(ROW(A1),4)=0,4,MOD(ROW(A1),4))) nope that wont work i thought it said c:f

**martindwilson** · 09-04-2011, 04:34 PM

ok try this , the lookup names must match your column names on sheet 1
=INDEX(Sheet1!$C$2:$K$1000,FLOOR(ROW(A4)/4,1),MATCH(LOOKUP(IF(MOD(ROW(A1),4)=0,4,MOD(ROW(A1),4)),{1,2,3,4},{"names","address","city","dob"}),Sheet1!$C$1:$K$1,0))