Autofill reference formula but repeating n times before incrementi
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
--
Biff
Microsoft Excel MVP
"Anton" wrote in message
...
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?
|