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!