This should be simple for someone

  • Thread starter Thread starter Marti
  • Start date Start date
M

Marti

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!
 
One way...

Entered in D24 and copied down as needed:

=INDEX('OPTIONS ASSEMBLIES'!E$1733:E$65536,ROWS(D$24:D24)*6-5)
 
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!
 
ROWS(D$24:D24)*6-5
What is this doing?
Click to expand...

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
 
Thank you! Thank you! Thank you!

This is going to work very well for me!
Great day to you!
 
Back
Top