Excel autofill problems

Question

I am an advanced novice at using Excel and I have a problem.

I am using 2 worksheets and want to refer cells from sheet1 to sheet2.

In sheet1 (named "Aktuel") I have a list of all my costumers' information such as name, address, phone number, etc. In sheet 2 I want to rearrange the information so it is a little easier on the eyes when printing out a list of customers.

I've arranged it so that the first customer uses rows 1-7 and columns A-G instead of a single row.

Right now part of worksheet2 looks like this:

A1: Text
A2: =Aktuel!G3
A3: =Aktuel!C3
A4: =Aktuel!F3
A5: Text
A6: Text
A7: Text

My problem is that when I select the cells and drag with the autofill option, the cells referring to the "Aktuel"-sheet jumps.

After Autofill it looks like this:

A8:  Text
A9:  =Aktuel!G10
A10: =Aktuel!C10
A11: =Aktuel!F10
A12: Text
A13: Text
A14: Text

where I instead want it to look like this:

A8:  Text
A9:  =Aktuel!G4
A10: =Aktuel!C4
A11: =Aktuel!F4
A12: Text
A13: Text
A14: Text

and then continue onward with:

A15: Text
A16: =Aktuel!G5
A17: =Aktuel!C5
A18: =Aktuel!F5
A19: Text
A20: Text
A21: Text

Is there a way to do this?

I'm sorry for the confusing way of explaining the problem. I don't quite have a rich Excel-vocabulary.

PS. I'm using Excel 2010 edition.

Answer 1

When you copy a relative cell reference down one row, the reference will only be adjusted by one row. If you copy the formula down 7 rows, the reference will be adjusted by 7 rows. That is how Excel works.

To achieve what you describe, you need a formula that adjusts the cell reference every 7th row only. It is a bit more complex that a simple =A1.

Try the following three formulas in the cells A2, A3 and A4. Then copy the cells from A1 to A7 and paste into A8, A15, etc.

A2: =INDEX(Aktuel!$G:$G,CEILING(ROW()/7,1)+2)

A3: =INDEX(Aktuel!$C:$C,CEILING(ROW()/7,1)+2)

A4: =INDEX(Aktuel!$F:$F,CEILING(ROW()/7,1)+2)

The Ceiling formula will return 1 in rows 1 to 7, 2 in rows 8 to 14, etc. Add 2 to return the desired row. Feed this as the Row argument for the Index function to return the nth row of the column specified.