Excel - Copy down

[JO]

Example :

-I have stuff in Column A from row 1 to 20.
-Now I create a formula in cell B1 that depends on the value in A1.
-I want to copy down the whole thing all the way down to B20.

Now if you do :

1. Ctrl-C
2. Shift (hold)
3. End
4. Down arrow
5. Release shift Enter

You will copy down your formula down to row 65,xxx whatever.

What I used to be able to do to stop the selection at row 20 was :

1. Ctrl-C
2. Shift (hold)
3. left Arrow (to select A1 and B1)
4. End
5. Down arrow
6. Right arrow
7. Enter

And I was all set. Now I can't do this on this new computer I got. What happens if I do what I described is the same thing as without selecting your column where you want the drag to stop, i.e. all the way down until the end of the workbook.

I'm getting really annoyed of that, especially that I know I can do what I want (South Park quote, yeah!). I looked in the Option, it's not there.

How do I fix this?? AHHHHH!!

[Gates Is Antichrist]

Shift-end-home or shift-ctrl-end, followed by left-arrowing as necessary, often works out most efficiently for me. (I know - your sheet may exceed 20 lines, etc....)

[JO]

Hmm, I didn't know about shift-end-home. It works! But then I filled column C with stuff from 1 to over 20 and if I do the trick, I get the selection up to whatever is in Column C, even if I select Column A before hitting end-home. Anyway, that works in the meantime. If anyone else has an idea, let me know. Thanks.

[Gates Is Antichrist]

The alternate silly thing I do is go to A1, go end-down, right-arrow once. That puts me at B20. I just type in a letter like X.

_Then_ I go about my copy/paste. At least there's a stopping point now. I do this so often now that it's almost automatic.

Lotus still seems to know when to stop when multicolumn "end-" navigating. Excel has always overshot, as far back as I can remember, when asymmetric.

[MathGuy]

Select B1.
Ctrl+C
Left.
Ctrl+Down.
Right.
Ctrl+Shift+Up.
Ctrl+V (or whatever variation of paste you wish to use).

[JO]

I hadn't thought about this one...Damn actuaries are smart! Thanks for the help guys, I think I'm all set now.

[Cho Da]

Uh, just double click the little black square in the lower right corner of the cell to copy.

[JO]

I give props to the copy master. By the way, I love your avatar and that quote is the best thing I've read in a while. THANK YOU INTERNET.

[whisper]

It sounds like your "Transition Navigation Keys" option has been turned off. It should be under the Tools - Option - Transition tab. Checking it will return Excel the way you used to do it.

[Dr T Non-Fan]

whisper is right.