Excel Autofill Frustrations 7

Christine74 · Jan 11, 2003

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 · Jan 12, 2003

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 · Jan 12, 2003

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 · Jan 12, 2003

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 · Jan 12, 2003

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 · Jan 12, 2003

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 · Jan 12, 2003

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 · Jan 13, 2003

Hi all

Excellent Function.

TKengine · Jan 14, 2003

IRstuff you have a star from me for your excelent post

ProjEngKLS · Jan 14, 2003

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

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Excel Autofill Frustrations 7

Christine74

Mechanical

Recommended for you

FH

Mechanical

brengine

Mechanical

IRstuff

Aerospace

FH

Mechanical

IRstuff

Aerospace

Christine74

Mechanical

Murali27

Structural

TKengine

Automotive

ProjEngKLS

Mechanical

Similar threads

Part and Inventory Search

Sponsor