| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to reference different worksheets in an autofill formula | Excel Discussion (Misc queries) | |||
| Repeating/incrementing dates 35039 times | Excel Discussion (Misc queries) | |||
| How do i use the same name multiple times in repeating worksheets | Excel Discussion (Misc queries) | |||
| reference autofill. | Excel Worksheet Functions | |||
| reference a repeating cell in a formula | Excel Worksheet Functions | |||
![Anton[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
