| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This should be simple for someone
Just not able to figure it out myself...I simply need the formula to pull a
cell in every 6th row into a new worksheet. When I copy down, it doesn't recognize to advance 6 rows. I want to bring these over without manually editing each cell....of course, with the amount of time I have tried to figure this out, I could have done it manually! (but I am stubborn!) I have just been using the "equal" and selecting the cell from the first sheet, but that is extremely tedious. Here is what I have ='OPTIONS ASSEMBLIES'!E1733 to Cat08 cell D24 ='OPTIONS ASSEMBLIES'!E1739 to Cat08 cell D25 I need E1745 for D26 and E1151 for D27 and so on, and so on, and so on for pages and pages This should be simple, right? Thanks smart people! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
This should be simple for someone
One way...
Entered in D24 and copied down as needed: =INDEX('OPTIONS ASSEMBLIES'!E$1733:E$65536,ROWS(D$24:D24)*6-5) -- Biff Microsoft Excel MVP "Marti" wrote in message ... Just not able to figure it out myself...I simply need the formula to pull a cell in every 6th row into a new worksheet. When I copy down, it doesn't recognize to advance 6 rows. I want to bring these over without manually editing each cell....of course, with the amount of time I have tried to figure this out, I could have done it manually! (but I am stubborn!) I have just been using the "equal" and selecting the cell from the first sheet, but that is extremely tedious. Here is what I have ='OPTIONS ASSEMBLIES'!E1733 to Cat08 cell D24 ='OPTIONS ASSEMBLIES'!E1739 to Cat08 cell D25 I need E1745 for D26 and E1151 for D27 and so on, and so on, and so on for pages and pages This should be simple, right? Thanks smart people! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
This should be simple for someone
Thanks Bif...I can't figure out WHY that works, and I have been reading the
help topic about the INDEX function....but it works! If I could understand why this part works: ROWS(D$24:D24)*6-5 I could modify for the next sheet where I need to pick every 7th row from the same source sheet. What is this doing? Thanks! "T. Valko" wrote: One way... Entered in D24 and copied down as needed: =INDEX('OPTIONS ASSEMBLIES'!E$1733:E$65536,ROWS(D$24:D24)*6-5) -- Biff Microsoft Excel MVP "Marti" wrote in message ... Just not able to figure it out myself...I simply need the formula to pull a cell in every 6th row into a new worksheet. When I copy down, it doesn't recognize to advance 6 rows. I want to bring these over without manually editing each cell....of course, with the amount of time I have tried to figure this out, I could have done it manually! (but I am stubborn!) I have just been using the "equal" and selecting the cell from the first sheet, but that is extremely tedious. Here is what I have ='OPTIONS ASSEMBLIES'!E1733 to Cat08 cell D24 ='OPTIONS ASSEMBLIES'!E1739 to Cat08 cell D25 I need E1745 for D26 and E1151 for D27 and so on, and so on, and so on for pages and pages This should be simple, right? Thanks smart people! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
This should be simple for someone
ROWS(D$24:D24)*6-5
What is this doing? ROWS(...) returns the count of rows referenced in its argument. ROWS(D$24:D24) = 1 As you drag copy the formula down the column the ROWS count will increment and the multiplication/subtraction operation will return the *relative* position number of the value in the referenced array E$1733:E$65536. This number tells the INDEX function to return the value found at that position: D24: ROWS(D$24:D24)*6-5 = 1*6-5 = 1 INDEX(E$1733:E$65536,1) = value from position 1 = E1733 D25: ROWS(D$24:D25)*6-5 = 2*6-5 = 7 INDEX(E$1733:E$65536,7) = value from position 7 = E1739 D26: ROWS(D$24:D26)*6-5 = 3*6-5 = 13 INDEX(E$1733:E$65536,13) = value from position 13 = E1745 D27: ROWS(D$24:D27)*6-5 = 4*6-5 = 19 INDEX(E$1733:E$65536,19) = value from position 19 = E1751 etc etc etc -- Biff Microsoft Excel MVP "Marti" wrote in message ... Thanks Bif...I can't figure out WHY that works, and I have been reading the help topic about the INDEX function....but it works! If I could understand why this part works: ROWS(D$24:D24)*6-5 I could modify for the next sheet where I need to pick every 7th row from the same source sheet. What is this doing? Thanks! "T. Valko" wrote: One way... Entered in D24 and copied down as needed: =INDEX('OPTIONS ASSEMBLIES'!E$1733:E$65536,ROWS(D$24:D24)*6-5) -- Biff Microsoft Excel MVP "Marti" wrote in message ... Just not able to figure it out myself...I simply need the formula to pull a cell in every 6th row into a new worksheet. When I copy down, it doesn't recognize to advance 6 rows. I want to bring these over without manually editing each cell....of course, with the amount of time I have tried to figure this out, I could have done it manually! (but I am stubborn!) I have just been using the "equal" and selecting the cell from the first sheet, but that is extremely tedious. Here is what I have ='OPTIONS ASSEMBLIES'!E1733 to Cat08 cell D24 ='OPTIONS ASSEMBLIES'!E1739 to Cat08 cell D25 I need E1745 for D26 and E1151 for D27 and so on, and so on, and so on for pages and pages This should be simple, right? Thanks smart people! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
This should be simple for someone
Thank you! Thank you! Thank you!
This is going to work very well for me! Great day to you! "T. Valko" wrote: ROWS(D$24:D24)*6-5 What is this doing? ROWS(...) returns the count of rows referenced in its argument. ROWS(D$24:D24) = 1 As you drag copy the formula down the column the ROWS count will increment and the multiplication/subtraction operation will return the *relative* position number of the value in the referenced array E$1733:E$65536. This number tells the INDEX function to return the value found at that position: D24: ROWS(D$24:D24)*6-5 = 1*6-5 = 1 INDEX(E$1733:E$65536,1) = value from position 1 = E1733 D25: ROWS(D$24:D25)*6-5 = 2*6-5 = 7 INDEX(E$1733:E$65536,7) = value from position 7 = E1739 D26: ROWS(D$24:D26)*6-5 = 3*6-5 = 13 INDEX(E$1733:E$65536,13) = value from position 13 = E1745 D27: ROWS(D$24:D27)*6-5 = 4*6-5 = 19 INDEX(E$1733:E$65536,19) = value from position 19 = E1751 etc etc etc -- Biff Microsoft Excel MVP "Marti" wrote in message ... Thanks Bif...I can't figure out WHY that works, and I have been reading the help topic about the INDEX function....but it works! If I could understand why this part works: ROWS(D$24:D24)*6-5 I could modify for the next sheet where I need to pick every 7th row from the same source sheet. What is this doing? Thanks! "T. Valko" wrote: One way... Entered in D24 and copied down as needed: =INDEX('OPTIONS ASSEMBLIES'!E$1733:E$65536,ROWS(D$24:D24)*6-5) -- Biff Microsoft Excel MVP "Marti" wrote in message ... Just not able to figure it out myself...I simply need the formula to pull a cell in every 6th row into a new worksheet. When I copy down, it doesn't recognize to advance 6 rows. I want to bring these over without manually editing each cell....of course, with the amount of time I have tried to figure this out, I could have done it manually! (but I am stubborn!) I have just been using the "equal" and selecting the cell from the first sheet, but that is extremely tedious. Here is what I have ='OPTIONS ASSEMBLIES'!E1733 to Cat08 cell D24 ='OPTIONS ASSEMBLIES'!E1739 to Cat08 cell D25 I need E1745 for D26 and E1151 for D27 and so on, and so on, and so on for pages and pages This should be simple, right? Thanks smart people! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
This should be simple for someone
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Marti" wrote in message ... Thank you! Thank you! Thank you! This is going to work very well for me! Great day to you! "T. Valko" wrote: ROWS(D$24:D24)*6-5 What is this doing? ROWS(...) returns the count of rows referenced in its argument. ROWS(D$24:D24) = 1 As you drag copy the formula down the column the ROWS count will increment and the multiplication/subtraction operation will return the *relative* position number of the value in the referenced array E$1733:E$65536. This number tells the INDEX function to return the value found at that position: D24: ROWS(D$24:D24)*6-5 = 1*6-5 = 1 INDEX(E$1733:E$65536,1) = value from position 1 = E1733 D25: ROWS(D$24:D25)*6-5 = 2*6-5 = 7 INDEX(E$1733:E$65536,7) = value from position 7 = E1739 D26: ROWS(D$24:D26)*6-5 = 3*6-5 = 13 INDEX(E$1733:E$65536,13) = value from position 13 = E1745 D27: ROWS(D$24:D27)*6-5 = 4*6-5 = 19 INDEX(E$1733:E$65536,19) = value from position 19 = E1751 etc etc etc -- Biff Microsoft Excel MVP "Marti" wrote in message ... Thanks Bif...I can't figure out WHY that works, and I have been reading the help topic about the INDEX function....but it works! If I could understand why this part works: ROWS(D$24:D24)*6-5 I could modify for the next sheet where I need to pick every 7th row from the same source sheet. What is this doing? Thanks! "T. Valko" wrote: One way... Entered in D24 and copied down as needed: =INDEX('OPTIONS ASSEMBLIES'!E$1733:E$65536,ROWS(D$24:D24)*6-5) -- Biff Microsoft Excel MVP "Marti" wrote in message ... Just not able to figure it out myself...I simply need the formula to pull a cell in every 6th row into a new worksheet. When I copy down, it doesn't recognize to advance 6 rows. I want to bring these over without manually editing each cell....of course, with the amount of time I have tried to figure this out, I could have done it manually! (but I am stubborn!) I have just been using the "equal" and selecting the cell from the first sheet, but that is extremely tedious. Here is what I have ='OPTIONS ASSEMBLIES'!E1733 to Cat08 cell D24 ='OPTIONS ASSEMBLIES'!E1739 to Cat08 cell D25 I need E1745 for D26 and E1151 for D27 and so on, and so on, and so on for pages and pages This should be simple, right? Thanks smart people! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
| Simple problem, simple formula, no FUNCTION ! | Excel Worksheet Functions | |||
| Simple lookup - or maybe not so simple - help! | Excel Worksheet Functions | |||
| Simple Simple Excel usage question | Excel Discussion (Misc queries) | |||
| Make it more simple or intuitive to do simple things | Charts and Charting in Excel | |||
Linear Mode
