creating a pattern for auto fill dfor referenced cells

**saxton** · 03-01-2015, 08:58 PM

I am having a problem getting autofill to follow my desired pattern while referencing cells on a different page.

I am building a worksheet with one master data sheet and several tabs compiling selected data into functional sheets. On one tab I need a row to go as follows: 'sheet1'!A1, 'sheet1'!B1, 'sheet1'!C1....'sheet1'!H1, 'sheet1'!A2, 'sheet1'!B2,'sheet1'!B2.....'sheet1"!H2, 'sheet1'!A3....etc. going like that continually.

Basically I need the pattern to continue to read 8 columns across before moving to the next row down (on the referenced page).

The problem I am facing when trying to do this is after manually building several rows then attempting to autofill, excel fills the row below with 8 columns but will not go 1 row down but 8. The result is: 'sheet1'!A1, 'sheet1'!B1, 'sheet1'!C1....'sheet1'!H1, 'sheet1'!A9, 'sheet1'!B9,'sheet1'!B9.....'sheet1"!H17, 'sheet1'!A16....etc.

Below explains more clearly:

What I want (simplified):

'sheet1'!A1
'sheet1'!B1
'sheet1'!C1
'sheet1'!A2
'sheet1'!B2
'sheet1'!C2
'sheet1'!A3
'sheet1'!B3
'sheet1'!C3

What I am getting:

'sheet1'!A1
'sheet1'!B1
'sheet1'!C1
'sheet1'!A4
'sheet1'!B4
'sheet1'!C4
'sheet1'!A7
'sheet1'!B7
'sheet1'!C7

How can I accomplish this task?

thanks

**Tony Valko** · 03-01-2015, 10:31 PM

See if this helps...

Data Range

	A	B	C	D	E
1	1	2	3		1
2	4	5	6		2
3	7	8	9		3
4					4
5					5
6					6
7					7
8					8
9					9
10	------	------	------	------	------

We want to reconfigure the range A1:C3 to a single column.

This formula entered in E1 and coped down to E9:

=OFFSET(A$1,INT((ROWS(E$1:E1)-1)/3),MOD(ROWS(E$1:E1)-1,3))

**saxton** · 03-02-2015, 02:59 AM

Perfect! a couple tweaks and it worked. Thank you

**Tony Valko** · 03-02-2015, 03:23 PM

You're welcome. Thanks for the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.