Autofill reference formula but repeating n times before incrementi

[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. 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

 

[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

 

[Anton]

Absolutely brilliant - Both you guys!

Fanx a lot!

 

[RagDyeR]

And we appreciate the feed-back.