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
Instead, I get "=A22", "=A42", etc. Positively infuriating!
How do I resolve this problem?
Thanks,
-Christine
RE: Excel Autofill Frustrations
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
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
=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
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
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
Thanks!
-Christine
RE: Excel Autofill Frustrations
Excellent Function.
RE: Excel Autofill Frustrations
RE: Excel Autofill Frustrations
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