a faster way? (keystroke shortcut question)

[Asala42]

Hey all,

This is minor but has been nagging at me for a bit. I think I've been using too many keystrokes for this everyday operation.

Say you have a range of data (A1:A5532) and you want to type apply a formula for each cell in the range adjacent (B1:B5532). How precicely do you go about filling the formula down through row 5532 (in this example)?

Here's how I normally do this (drawn out in a painfully slow step-by-step description):

1.Type the formula in the first row.
2.Click on A1
3.Hit the keys Shift+End+DownArrow (highlighting A1:A5532, making the last row visible).
4.type a random character in B5532
5.Ctrl+Home back to A1
6.Select B1 and hit Ctrl+C (copy)
7.Shift+End+Down Arror to highlight B1:B5532
8.Ctrl+V (paste).

Is this how you all do this or am I doing a bunch more steps than I need? As a sidenote, I checked the help file for shortcut key combos and didn't find a good one (unless I'm blind and just missed it).

Thanks all! (yes I know this question is really really really really minor)

Adam

[Jack in the UK]

try:

enter formula in B1 ie =A1.... whatever
press enter
arrow up
ctrl+c
arrow left
crt+shift+arrow down
arrow right ctrl+shift+arrow up
crt+v
hit f9

is that any quicker?

[IML]

On 2002-05-21 13:53, Asala42 wrote:
Hey all,

This is minor but has been nagging at me for a bit. I think I've been using too many keystrokes for this everyday operation.

Say you have a range of data (A1:A5532) and you want to type apply a formula for each cell in the range adjacent (B1:B5532). How precicely do you go about filling the formula down through row 5532 (in this example)?

Here's how I normally do this (drawn out in a painfully slow step-by-step description):

1.Type the formula in the first row.
2.Click on A1
3.Hit the keys Shift+End+DownArrow (highlighting A1:A5532, making the last row visible).
4.type a random character in B5532
5.Ctrl+Home back to A1
6.Select B1 and hit Ctrl+C (copy)
7.Shift+End+Down Arror to highlight B1:B5532
8.Ctrl+V (paste).

Is this how you all do this or am I doing a bunch more steps than I need? As a sidenote, I checked the help file for shortcut key combos and didn't find a good one (unless I'm blind and just missed it).

Thanks all! (yes I know this question is really really really really minor)

Adam

Type in your formula in B1 hit enter.
Select cell B1
Double click on the handle in the lower right corner (this will fill as far as you have rows populated in col A

[Asala42]

Jack - good call. Apparently, I guess I just felt like hitting Ctrl+home just for fun.

IML - You Rock!!

I KNEW there was an answer like that somewhere. You have removed a thorn that's been in my side for a long time.

Thanks all!
Adam

[Chris Davison]

Asala,

Adding to Ian's lifesaver.....

you can use this technique to navigate around your populated range :

say your range is A1 to X100....

clicking in any cell in column A : move the mouse so it hovvers over the bottom border - it will turn into an arrow, now double click : it will take you to the bottom of that range

you'll now be in A100... hovver over the right border, the arrow will appear again.... double click and it will transport you over to the right-most cell of the range quicker than any Star Trek movie....cell Z100

hovver on the top of this cell, and do your stuff.... it will take you back up to the top of column Z...cell Z1

you get the picture...



Hope this helps
Chris

marker mouseclick navigation

[Mark O'Brien]

F4 changes between absolute and relative cell references.

Sorry, absolutely no help or relevance, but that's a shortcut I know. It had to be done.

[Jack in the UK]

Hi Mark its Jack

I know know why you moved to the US, you support Celtic and they through you out as your english, we did not want you as them type of cracks are aweful so only left option was left.. IE US.

Take care friend... PS less wise crak PLEEESE..

Celtic wont win the world cup NO MATTER WHAT.

[Mark O'Brien]

On 2002-05-29 15:07, Jack in the UK wrote:
Hi Mark its Jack

Yes, I understand this, your username beside your post sort of gave it away. I would guess that you're from South London as well.

I know know why you moved to the US, you support Celtic and they through you out as your english, we did not want you as them type of cracks are aweful so only left option was left.. IE US.

Not quite following you here.

I do support Celtic, that is true. I did move to the US, that also is true. I am not English as well you know. (but I gotta lotta mates that are and I always give to charidee mate, but I don't like to talk about it). I am not sure what kind of "cracks" that you are talking about.

Take care friend... PS less wise crak PLEEESE..

Are you questioning my manner of posting on the board? (a whole week after my last posting to this thread?)
There was no crak in my comment. I merely saw the word "shortcut" in the posting, I had recently found out that "F4" switches between absolute and relative references and wished to share this knowledge with our friends around the world.

Is this redundant and of no use to the thread? Yes it is. Did I state this in my original post in this thread? Yes, I did. Was the thread dead and buried before I posted. Yes, it was. The OP had a satisfactory solution provided by Asala42 with a nice addendum by Mr. Davison. (a mutual good friend of ours)

Celtic wont win the world cup NO MATTER WHAT.

Celtic have as much chance of winning the World Cup as any club side in the world. i.e. none.

Thanks for your attention Jack. Please don't comment on my postings. I can drag up several rather nasty posts of yours directed to members of this board. (not to mention the number of times that you post a reply like "I have done this before", yet never offer the solution on this site)

If I have misread your post or have been confused by your intentions, I do apologise. However I offer the excuse that I am not fluent in Jackanese.

You know that I do not attack your postings Jack, though you say yourself that many do, so please do not attack my postings.

[Anne Troy]

Hey, Mark.

F4 will certainly change from relative to absolute any cells you have selected in the formula bar.

However, F4 in the spreadsheet itself will repeat the last command. So, if you're bored one day, you can color a cell yellow, click on another cell and hit F4, another and F4. It's also a very cool way to format entire columns as, for instance, numbers, when you can't see them all at once.

F4 is the greatest key in Office. It works in Word too.



I am personally ALL into shortcut keys!

[PDuPre']

Ditto the Shortcut keys. Slows me down to take my hands off the keyboard and touch the mouse.
Just wondering how you do a copy and paste. I hit CTRL-Insert to copy and Shift-Insert to paste. Using the Ctrl key on the left side of the space bar.
Never really got comfortable using the CTRL-C CTRL-V as I always use the CTRL key on the left of the space bar. It twists my hand too much, and seems to be less precise for me. When you use CTRL-C / CTRL-V (anyone?) do you use the CTRL key on the right side of the space bar?

By the way, CTRL-Delete will cut. I have found that these will work in some places that CTRL-C, CTRL-V will not work. Virtually every application supports it.

Philip