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.)
4 thoughts on “Quick Tip – Set RefEdit boxes to Edit mode”
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.
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.
Nice - thanks - have always been frustrated with that and never accidentally happened upon the solution or read about it before - much appreciated
I use the table feature in XL2007 a lot. Very often I do not use the default range that XL suggests and I want to change the range. It seems so easy to use the cursor keys to go the cells I want to adjust but ... XL then changes the range. Which is not what I want.
Thanks for the tip to use F2 first and then cursor to the cells to adjust the range.