Quick Tip – Set RefEdit boxes to Edit mode
I don’t know how often this happens to you but it drives me crazy…
Assume that I’ve got a formula set up in a conditional format, named range or some other place that has a “RefEdit” control on it. I highlight the last character in $T$52 and change it to $T$51, then press the right arrow so I can arrow over and change the $AE$51 to $AE$52. What happens? I get a cell reference in my formula!
The problem is that the refEdit control (signified by the little box to the right of the typing area) is still in formula creation mode, not editing mode.
To change this, press F2, just like you’d do to edit a cell from the grid. At that point you’re toggled into edit mode and you can move around using the mouse as much as you’d like.
The biggest challenge is remembering to do it BEFORE you hit your arrow key. Unfortunately there isn’t a way to set the default behavior to be in edit mode. (At least, not that I’m aware of.)
on July 5th, 2009 at 9:46 am
A useful tip, one that I am amazed when people don’t know as the same fix applies to loads of places where this refEdit control appears. One that sometimes catches me out is the formula editing box of a conditional format when you actually want to change the formula used. Behaviour there is slightly different as it puts +$X$29 (or whatever) which might be syntactially valid in the formula, although almost certainly not what you want.
The annoying instance in the name manager dialogue (both old and new style) is not so bad, I have got used to doing F2 before editing in there because I so often use names based on formulae (such as for dynamic ranges).
A visual indicator to remind you which mode you are in would be useful, I think.
on July 5th, 2009 at 11:30 am
I visual indicator would be useful, I agree. Couple that with the ability to set a default and it would help a lot of people, I think.
on July 8th, 2009 at 9:41 am
Nice – thanks – have always been frustrated with that and never accidentally happened upon the solution or read about it before – much appreciated