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?
 
Ad

Advertisements

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top