×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

Excel Autofill Frustrations
7

Excel Autofill Frustrations

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

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

RE: Excel Autofill Frustrations

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

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.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close