Incrementing a formula by X number

B

Bowbender

Hello All,

I have yet another one which I am sure most of you know the answer to.
To set it up, here it goes. I have a worksheet named Results and in
this worksheet I have around 25,000 different numbers in the B column.
Out of all those, I want to get every 20th one and put that value into a
worksheet called Sheet2 lets say. I obviously don't want to hand type
in

=Results!B20
=Results!B40
=Results!B60
etc...

Is there a way I can use a more sophisticated(sp?) formula, so that I
can just drag down and have it fill automatically?

Thanks in advance
 
S

swatsp0p

On sheet 2 enter this formula in A1

=INDEX(Results!$B$1:$B$25000,ROW()*20)

copy this down 1250 rows

A1 will return the value in B20 from Results! (row 1*20)
A2 will return the value in B40 from Results! (row 2*20)
...and so on...

HTH

Bruce
 
B

Bowbender

swatsp0p said:
On sheet 2 enter this formula in A1

=INDEX(Results!$B$1:$B$25000,ROW()*20)

copy this down 1250 rows

A1 will return the value in B20 from Results! (row 1*20)
A2 will return the value in B40 from Results! (row 2*20)
...and so on...

HTH

Bruce
Click to expand...

Bruce,

Thanks! I should have mentioned, because I think it will make a
difference. The formula starts in B2 and will be drug down from there.
Will this change the formula? While it is "pulling" data over, it is
getting the wrong ones.

Thanks,

Dave
 
B

Bowbender

swatsp0p said:
On sheet 2 enter this formula in A1

=INDEX(Results!$B$1:$B$25000,ROW()*20)

copy this down 1250 rows

A1 will return the value in B20 from Results! (row 1*20)
A2 will return the value in B40 from Results! (row 2*20)
...and so on...

HTH

Bruce
Click to expand...

I think I figured it out. For the first one I used
=INDEX(Results!$B$1:$B$25000,ROW()*10)
then
=INDEX(Results!$B$1:$B$25000,ROW()*20)
and drug this down.

Thanks for your help. You can't imagine, or maybe you can, how muc
time this saves.

Dav
 
Top