This should be simple for someone

[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!

 

[T. Valko]

One way...

Entered in D24 and copied down as needed:

=INDEX('OPTIONS ASSEMBLIES'!E$1733:E$65536,ROWS(D$2424)*6-5)

 

[Marti]

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$2424)*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]

ROWS(D$2424)*6-5

What is this doing?

ROWS(...) returns the count of rows referenced in its argument.

ROWS(D$2424) = 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$2424)*6-5 = 1*6-5 = 1
INDEX(E$1733:E$65536,1) = value from position 1 = E1733

D25: ROWS(D$2425)*6-5 = 2*6-5 = 7
INDEX(E$1733:E$65536,7) = value from position 7 = E1739

D26: ROWS(D$2426)*6-5 = 3*6-5 = 13
INDEX(E$1733:E$65536,13) = value from position 13 = E1745

D27: ROWS(D$2427)*6-5 = 4*6-5 = 19
INDEX(E$1733:E$65536,19) = value from position 19 = E1751

etc
etc
etc

 

[Marti]

Thank you! Thank you! Thank you!

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

 

[T. Valko]

You're welcome. Thanks for the feedback!