|
Autofill reference formula but repeating n times before incrementi
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? |
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? |
Autofill reference formula but repeating n times before incrementi
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 ! ----------------------------------------------------------------------------------------------- "T. Valko" wrote in message ... 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? |
Autofill reference formula but repeating n times before increm
Absolutely brilliant - Both you guys!
Fanx a lot! "RagDyeR" wrote: 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 ! ----------------------------------------------------------------------------------------------- "T. Valko" wrote in message ... 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? |
Autofill reference formula but repeating n times before increm
And we appreciate the feed-back.
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Anton" wrote in message ... Absolutely brilliant - Both you guys! Fanx a lot! "RagDyeR" wrote: 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 ! ----------------------------------------------------------------------------------------------- "T. Valko" wrote in message ... 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? |
| All times are GMT +1. The time now is 10:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com