Excel Autofill Frustrations 7

[Christine74]

I have a column with several thousand rows of data in an Excel spreadsheet. I need to make a seperate column containing ONLY every 20th row of the aforementioned column. So I type in one cell "=A20", then go to the cell below it and type in "=A40", and then I select both cells and drag down using autofill, hoping to complete the column with "=A60", "=A80", ... "=A6480".

Instead, I get "=A22", "=A42", etc. Positively infuriating!

How do I resolve this problem?

Thanks,

-Christine

 

[FH]

Dear
The below formula is the key of your problem.
=OFFSET($A$1;ROW()*20-1;0)
The mentioned formula assumes your data lies in column A.

Farzad

 

[brengine]

As long as Cell A1..A19 are blank (and A21..A39, etc...), just highlight A1..A20, then paste it in at A21, A41, etc...
Maybe once you've pasted up to maybe A100, you could select A1..A100, and paste in at A101, A201, etc...making it easier to fill in those "thousands" of lines.

Ken

 

[IRstuff]

For you application of getting A20,A40,A60,etc, FH's equation needs to be modified to:

=OFFSET(a1,row()*19,0)

and then copied downwards as needed.

The first row references A20 (offset of 19 from row=1)
2nd row references A40 (offset of 38 from row=2)
3rd row references A60 (offset of 57 from row=3)

TTFN

 

[FH]

Dear IRstuff,
I tried your formula but an error generated because of commas instead of semicolons. Also I don't know what was the problem with my formula!

Farzad

 

[IRstuff]

Farzad,

Is your Excel a non-US install? I'm not quite familiar with the subtleties of foreign language installations, but the US version has commas separating individual parameters in a parameter list such as that for OFFSET.

As for your formula, there was nothing wrong with it for the first entry, in both cases, the first offset is 19. However, the second offset would be 20*2-1, which is 39, instead of the required 38. Likewise, the third would wind up being 20*3-1=59, instead of the desired 57.
TTFN

 

[Christine74]

Thanks for the help, guys. I wasn't aware of the OFFSET function, so I was about ready to type everything out by hand. Glad I didn't have to do that.

Thanks!

-Christine

 

[Murali27]

Hi all

Excellent Function.

 

[TKengine]

IRstuff you have a star from me for your excelent post

 

[ProjEngKLS]

Another slightly twisted way to do this is to use the Replace function.

Instead of entering the formula "=B20" just enter the text "B20". Then below this enter "B40". Then use the autofill to populate the rest of your cells. It will fill in B60, B80, B100, B120, etc.

Then use the REPLACE function in the EDIT menu. Replace "B" with "=B" in the column you previously populated with the text. The result is everthing will become formulas!

Good Luck,

ProjEngKLS