# Autofill reference formula but repeating n times before incrementi

A

#### Anton

I have one sheet with Col A having 100 numberic values (Item Numbers)
In my second sheet, I have 7 rows with variable data in Col A to Col E.
I need these 7 rows to repeat for each of the values in sheet 1 (adding the
value of the Item Number into Col F).
I.e. have the 7 rows with value of Sheet1!A1 in Col F then repeat the same 7
rows, but this time have the value of Sheet1!A2 in Col F.
Ending up with 700 rows (7 for each Item Number)
I want to drag the row values down, having autofill increment the cell
reference to Sheet 1 only after every 7th row.
Is this possible?

T

#### T. Valko

Try something like this:

=INDEX(Sheet1!A:A,CEILING(ROWS(A\$1:A1)/7,1))

This will repeat the value of Sheet1 A1 7 times then
Sheet1 A2 7 times then
Sheet1 A3 7 times then
Sheet1 A4 7 times then
Sheet1 A5 7 times then
etc
etc

R

#### RagDyeR

Another way, with one less function call:

=INDEX(Sheet1!A:A,ROWS(\$1:7)/7)
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Try something like this:

=INDEX(Sheet1!A:A,CEILING(ROWS(A\$1:A1)/7,1))

This will repeat the value of Sheet1 A1 7 times then
Sheet1 A2 7 times then
Sheet1 A3 7 times then
Sheet1 A4 7 times then
Sheet1 A5 7 times then
etc
etc

A

#### Anton

Absolutely brilliant - Both you guys!

Fanx a lot!