Editing named ranges

Topic

New Reply

Hi,

This is a nuisance issue rather than any big problem.

I have a lot of named ranges- some of them cover 5 or 6 different areas on the worksheet.

When I want to change the cells in the range (Insert, Name, Define, select the range, and click on the “refers to”), I have trouble using the keyboard to edit the bits which extend beyond the end of the box. When I use arrow keys, they change values in the range rather than moving the cursor. It sometimes takes a number of aborted attempts to get it right.

Is there a way to edit more easily?

Reply | Quote

Replies

Geoff,
When you click in the Refers to box are you in Edit Mode?
Check the status bar. If not, press F2.

Hope this is helpful.
Ken

Reply | Quote

Ken,

The “Refers To” box is in the “Define Name” Dialog Box. Pressing F2 does nothing.

Actually, this probably goes beyond just this one function. I often find the built-in dialogs restrictive, in that you cannot resize.

Reply | Quote

F2 switches between Enter and Edit modes for me in Excel 2000.

Reply | Quote

Hi again,

Strange, what you say. After I click on the refers to box in the Define Name Dialog box, my status bar shows that F2 toggles between “Enter” and “Edit”. In the Edit mode I have no problem editing in the Refers To box.

Reply | Quote

Ken,

OK, I see.

I didn’t see anything happening- I expected something like when you press “F2” on a cell. And I don’t normally have my status bar displayed. But when I press on the arrow keys after I press on F2, the move around as I expect. That will be a great time-saver- thanks.

BTW, I did something rather bad.

For the record- “Excel 2000 SR1”.

Reply | Quote

Thanks,

It’s really useful hint to press F2. It helps entering ranges in other dialogs too!!!

Anu

Reply | Quote

Hey, thank fro me too. This was bugging me. I was copying what I had in the box, pasting it somewhere, editing it, and pasting it back.

Reply | Quote

I’ve run into this several times — the ‘Refers to’ argument extends well past the box and I need to add more at the end. The only way I’ve gotten around this is to use the mouse to put the cursor somewhere in the visible part of the reference and then do a ‘highlight/drag’ to the right. The reference will scroll to the end. I then position the insert bar at the end (to remove the highlighting from the other parts of the reference) and enter a comma and either type the additional range or use the mouse to highlight it on the worksheet. It’s a bit of a nuisance but it works. (Excel97 SR2)

Reply | Quote

Gene,

kjktoo’s solution (pressing F2) worked well- I can use arrows in the normal way- I just didn’t realise at first that when I pressed F2, something happened (F2 in ewxplorer or Excel in a cell gives a much more positive feedback).

Reply | Quote