Copying a dynamic range

[gannon_w]

So I have data in A1:A1000
In the B column I need to have every 10th value from A so when I copy the function down I will have 100 values in the B column. Is this an indirect function?

THANKS in advanced!

Dr. Gannon

[GeneralDisarray]

Would you like the values to appear in the same row?


Like, B10 = A10, B20 = A30, but in between those values, just blanks in the B column?

If that's it, put this into cell B1 and copy down to B1000: =IF(MOD(ROW(),10)=0,A1,"")



IF you mean, you want the 100 values to show up in B1:B100, use this formula: =OFFSET(A$1,ROW(A1)*10-1,0)
Put that in B1 and copy down through B100

[gannon_w]

No
b1=a1
b2=a11
b3=a21

[GeneralDisarray]

Ok, then try this:

=OFFSET(A$1,(ROW(A1)-1)*10,0)

Put that in Cell B1, then copy down as needed.

[Tony Valko]

Here's another one...

Entered in B1 and copied down to B100:

=INDEX(A$1:A$1000,ROWS(B$1:B1)*10-10+1)

B1 = A1
B2 = A11
B3 = A21
B4 = A31
etc
etc

[gannon_w]

That's it...I can never remember the indirect/offset functions...Also, love the name General!

[GeneralDisarray]

Glad it helped, thanks for the Index() alternative Tony

[Tony Valko]