×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Excel Autofill Frustrations7

## Excel Autofill Frustrations

(OP)
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

### RE: Excel Autofill Frustrations

2
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.
:)

### RE: Excel Autofill Frustrations

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

### RE: Excel Autofill Frustrations

4
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

### RE: Excel Autofill Frustrations

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!
:)

### RE: Excel Autofill Frustrations

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

### RE: Excel Autofill Frustrations

(OP)
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

### RE: Excel Autofill Frustrations

Hi all

Excellent Function.

### RE: Excel Autofill Frustrations

IRstuff you have a star from me for your excelent post

### RE: Excel Autofill Frustrations

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

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!