Register  |   Log In  |  
Sign up to our weekly newsletter    
Follow us on   
Search   
Forum Home Register for Free! Log In Moderator Tickets FAQ Users Online

Excel query

 
Logged in as: Guest
  Printable Version
All Forums >> [Film Forums] >> Knowledge Base >> Excel query Page: [1]
Login
Message << Older Topic   Newer Topic >>
Excel query - 9/5/2012 2:20:14 PM   
SWOTBM


Posts: 1998
Joined: 6/5/2007
I have an annoying issue with Excel that I was hoping someone could help me out with. If I am typing in a cell and I hit the left arrow key, it automatically takes me to the adjacent cell rather than going back a space in the cell I was just typing in. Anyone know of a way to change this default setting?

Many thanks in advance

_____________________________

PSN: light_brigade
Gamertag: chauders
Post #: 1
RE: Excel query - 9/5/2012 2:37:14 PM   
elab49


Posts: 54620
Joined: 1/10/2005
Use the backspace rather than the left key - backspace will operate within the cell, the arrow key between cells. 

_____________________________

Lips Together and Blow - blogtasticness and Glasgow Film Festival GFF13!

quote:

ORIGINAL: Deviation] LIKE AMERICA'S SWEETHEARTS TOO. IT MADE ME LAUGH A LOT AND THOUGHT IT WAS WITTY. ALSO I FEEL SLOWLY DYING INSIDE. I KEEP AGREEING WITH ELAB.


Annual Poll 2013 - All Lists Welcome

(in reply to SWOTBM)
Post #: 2
RE: Excel query - 9/5/2012 2:50:53 PM   
SWOTBM


Posts: 1998
Joined: 6/5/2007

quote:

ORIGINAL: elab49

Use the backspace rather than the left key - backspace will operate within the cell, the arrow key between cells


So there's no way to change that? I don't want to delete a whole load of text when I only want to insert one letter.

_____________________________

PSN: light_brigade
Gamertag: chauders

(in reply to elab49)
Post #: 3
RE: Excel query - 9/5/2012 3:00:53 PM   
Professor Moriarty

 

Posts: 10466
Joined: 6/10/2005
From: the waters of Casablanca
Press F2 to enable edit cell function first.

(in reply to SWOTBM)
Post #: 4
RE: Excel query - 9/5/2012 3:04:18 PM   
elab49


Posts: 54620
Joined: 1/10/2005
I'm not sure if they would given that's what the back space key is for, going back one space. If you've got a longer line of text in the cell then if you place the cursor in the cell with your mouse, the back and forward keys do work within the cell then (when it's in edit mode). Sorry I can't be more help - I can't find any option to change it within my version of Excel.


_____________________________

Lips Together and Blow - blogtasticness and Glasgow Film Festival GFF13!

quote:

ORIGINAL: Deviation] LIKE AMERICA'S SWEETHEARTS TOO. IT MADE ME LAUGH A LOT AND THOUGHT IT WAS WITTY. ALSO I FEEL SLOWLY DYING INSIDE. I KEEP AGREEING WITH ELAB.


Annual Poll 2013 - All Lists Welcome

(in reply to SWOTBM)
Post #: 5
RE: Excel query - 9/5/2012 3:07:26 PM   
SWOTBM


Posts: 1998
Joined: 6/5/2007
^^^ Thanks. Is there anyway that I can turn that on permanently instead of having to hit F2 before typing though?

_____________________________

PSN: light_brigade
Gamertag: chauders

(in reply to Professor Moriarty)
Post #: 6
RE: Excel query - 9/5/2012 3:29:44 PM   
elab49


Posts: 54620
Joined: 1/10/2005
Can I ask why you don't want to use the back space key (sorry, not being cheeky I'm just envisaging you having a keyboard without one!)

_____________________________

Lips Together and Blow - blogtasticness and Glasgow Film Festival GFF13!

quote:

ORIGINAL: Deviation] LIKE AMERICA'S SWEETHEARTS TOO. IT MADE ME LAUGH A LOT AND THOUGHT IT WAS WITTY. ALSO I FEEL SLOWLY DYING INSIDE. I KEEP AGREEING WITH ELAB.


Annual Poll 2013 - All Lists Welcome

(in reply to SWOTBM)
Post #: 7
RE: Excel query - 9/5/2012 3:41:36 PM   
SWOTBM


Posts: 1998
Joined: 6/5/2007

quote:

ORIGINAL: elab49

Can I ask why you don't want to use the back space key (sorry, not being cheeky I'm just envisaging you having a keyboard without one!)


Sure. If I'm typing a sentence in a cell (e.g. ''I'm just envisaging you having a keybard without'') I much prefer to just hit left a couple of times and insert an ''o'' then hit end, instead of deleting ''ard without'' just to insert the ''o'' and then retype the ''ard without''. Granted I could use my mouse to put the cursor where the typo is, but it seems so much easier to use the arrows (at least in Word anyway )

_____________________________

PSN: light_brigade
Gamertag: chauders

(in reply to elab49)
Post #: 8
RE: Excel query - 9/5/2012 8:03:49 PM   
Super Hans


Posts: 2402
Joined: 30/9/2005
From: Watford
If you've got the cell selected (which obviously you will if you're typing in it), if you click in the formula bar, you can edit the contents from there, using the cursor keys if necessary?

_____________________________

"Its staring at you in the face Mark, there's only one more sex to try..."

(in reply to SWOTBM)
Post #: 9
RE: Excel query - 22/5/2012 10:50:11 AM   
Gram123

 

Posts: 5537
Joined: 19/1/2006
From: Reino Unido
quote:

ORIGINAL: Professor Moriarty
Press F2 to enable edit cell function first.

Double-clicking the cell does the same thing, but if you're working mainly on keyboard, the Prof's suggestion seems your best bet.

If you're in a cell, in Edit Mode, pressing Enter to go to the next cell "saves" the cell's contents and takes you out of edit mode, so you wouldn't really want it to be permanently switched on, as you'd be stuck in the one cell. Plus, if there were a way to force edit mode to be permanently "on", you won't be able to run any macros or save etc.
What you're really asking for is for Edit Mode to be on until you press Enter or Tab or click to another cell, and then back on as soon as your in the next cell. AFAIK it can't be done - there's no option for it in the menus, and you don't seem to be able to toggle Edit Mode to "on" in VBA.

_____________________________

Gram123's Top Songs Project

(in reply to Super Hans)
Post #: 10
RE: Excel query - 22/5/2012 3:17:23 PM   
Chief


Posts: 7778
Joined: 30/9/2005
From: Banshee
Stop being so fucking lazy and just use your mouse!

(in reply to SWOTBM)
Post #: 11
RE: Excel query - 7/5/2013 9:05:44 PM   
superdan


Posts: 8303
Joined: 31/7/2008
I am hoping any Excel bods may be able to help me with an issue I'm having. I'm currently putting together a spreadsheet for rotas at work, and I've got it pretty much sorted apart from one thing. When I input the start and finish times for shifts I need them to be rounded to .25 multiples rather than minutes, and I haven't a clue how to do it.

For instance:

If I input a 9.00 start in one column, and a 16.15 finish in the next, the 'total' column at the end shows 7.15 (i.e. 7 hours and 15 minutes) rather than 7.25 (7 and a quarter hours). This makes the total hours column virtually unreadable when a weeks worth of shifts are put in.

I have 2 questions: a) is it possible to rectify this; and b) is it possible to rectify this without having to re-do all the currently existing formulae I've done (e.g. set up another column to 'decimalise' the currently existing total column)? If it's any help here's an example of a formula I have at the moment for a row the 'total' column (fairly simple - finish times minus start times, all added together): =(E6-D6)+(G6-F6)+(I6-H6)+(K6-J6)+(M6-L6)+(O6-N6)+(Q6-P6)

Many thanks to any genius who is able to help me sort this out. I shall even put said genius on my Christmas card list.

(in reply to Chief)
Post #: 12
RE: Excel query - 7/5/2013 9:31:56 PM   
elab49


Posts: 54620
Joined: 1/10/2005
=INT(cell)*24+HOUR(cell)+ROUND(MINUTE(cell)/60,2)

Do your substraction to get 7:15
In the cell beside that use the formula above, using the cell reference for the cell with 7:15
Convert THAT cell to 'general'

_____________________________

Lips Together and Blow - blogtasticness and Glasgow Film Festival GFF13!

quote:

ORIGINAL: Deviation] LIKE AMERICA'S SWEETHEARTS TOO. IT MADE ME LAUGH A LOT AND THOUGHT IT WAS WITTY. ALSO I FEEL SLOWLY DYING INSIDE. I KEEP AGREEING WITH ELAB.


Annual Poll 2013 - All Lists Welcome

(in reply to superdan)
Post #: 13
RE: Excel query - 7/5/2013 9:57:43 PM   
superdan


Posts: 8303
Joined: 31/7/2008
quote:

ORIGINAL: elab49

=INT(cell)*24+HOUR(cell)+ROUND(MINUTE(cell)/60,2)

Do your substraction to get 7:15
In the cell beside that use the formula above, using the cell reference for the cell with 7:15
Convert THAT cell to 'general'



I've given that a go, and it's come up with 172.8 I'm obviously doing something fundamentally wrong, but I can't see what. In the (cell) in the formula above, I assume it will be the same cell reference for each one (in this case, =INT(r6)*24+HOUR(r6)+ROUND(MINUTE(r6)/60,2), with r6 being the 'total' column that I want to have 7.25 in the column next to it)?

Excel makes me

(in reply to elab49)
Post #: 14
RE: Excel query - 7/5/2013 10:08:25 PM   
elab49


Posts: 54620
Joined: 1/10/2005
As it happened I'd a couple of spreadsheets opened tidying them up for someone's project and this is one of the early formulas I'd put in.

What I have is this - follow exactly then see if there is a problem?

Choose a clean unformatted sheet and try the following.

A1 to A3 - format cells Time hh:mm:ss (you can play about with the formats, but it's worth seeing if this works first)

A1 09:00:00
A2 16:15:00
A3 A2-A1 (answer 07:15:00)

In cell A4 type =INT(a3)*24+HOUR(a3)+ROUND(MINUTE(a3)/60,2)

It will probably show 06:00:00
Format cell - choose general. That should change to 7.25



_____________________________

Lips Together and Blow - blogtasticness and Glasgow Film Festival GFF13!

quote:

ORIGINAL: Deviation] LIKE AMERICA'S SWEETHEARTS TOO. IT MADE ME LAUGH A LOT AND THOUGHT IT WAS WITTY. ALSO I FEEL SLOWLY DYING INSIDE. I KEEP AGREEING WITH ELAB.


Annual Poll 2013 - All Lists Welcome

(in reply to superdan)
Post #: 15
RE: Excel query - 7/5/2013 10:23:15 PM   
superdan


Posts: 8303
Joined: 31/7/2008
Yeah that worked fine. I have no idea how to apply this to the spreadsheet I have, but at least it's a small victory

(in reply to elab49)
Post #: 16
RE: Excel query - 7/5/2013 11:07:11 PM   
elab49


Posts: 54620
Joined: 1/10/2005
It's maybe just in the current formatting of the dates. You can actually change the time format in the example above, so I'm sure there's something that'll work out

_____________________________

Lips Together and Blow - blogtasticness and Glasgow Film Festival GFF13!

quote:

ORIGINAL: Deviation] LIKE AMERICA'S SWEETHEARTS TOO. IT MADE ME LAUGH A LOT AND THOUGHT IT WAS WITTY. ALSO I FEEL SLOWLY DYING INSIDE. I KEEP AGREEING WITH ELAB.


Annual Poll 2013 - All Lists Welcome

(in reply to superdan)
Post #: 17
Page:   [1]
All Forums >> [Film Forums] >> Knowledge Base >> Excel query Page: [1]
Jump to:





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts


 
Movie News  |  Empire Blog  |  Movie Reviews  |  Future Films  |  Features  |  Video Interviews  |  Image Gallery  |  Competitions  |  Forum  |  Magazine  |  Resources
 
Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI

0.516