Incrementing a formula by X number
 

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


--
Bowbender
------------------------------------------------------------------------
Bowbender's Profile: http://www.excelforum.com/member.php...fo&userid=9611
View this thread: http://www.excelforum.com/showthread...hreadid=469532

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=469532

swatsp0p Wrote:
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

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


--
Bowbender
------------------------------------------------------------------------
Bowbender's Profile: http://www.excelforum.com/member.php...fo&userid=9611
View this thread: http://www.excelforum.com/showthread...hreadid=469532

swatsp0p Wrote:
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

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 much
time this saves.

Dave


--
Bowbender
------------------------------------------------------------------------
Bowbender's Profile: http://www.excelforum.com/member.php...fo&userid=9611
View this thread: http://www.excelforum.com/showthread...hreadid=469532