Editing an Excel Formula with assigned range names
Editing an Excel Formula with assigned range names
(OP)
When I click on the formula window to edit an Excel formula (2007), the cell range names revert to their col-row cell references. Is there a setting that will make the cell range names stay put while I edit the formula?
RE: Editing an Excel Formula with assigned range names
TTFN

FAQ731-376: Eng-Tips.com Forum Policies
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers
Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
RE: Editing an Excel Formula with assigned range names
Go to Options-Advanced
Right at the end are Lotus 123 compatibility settings.
If these are ticked, un-tick them, and that should fix it.
If they are not ticked, I can't help.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Editing an Excel Formula with assigned range names
The Lotus compatibility was unchecked. I thought that this would be an easy question. I tried saving my xls spreadsheet to xlsx to see if that would help. Frustrated???
RE: Editing an Excel Formula with assigned range names
TTFN

FAQ731-376: Eng-Tips.com Forum Policies
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers
Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
RE: Editing an Excel Formula with assigned range names
Here is one that does not keep the range names when editing.
RE: Editing an Excel Formula with assigned range names
RE: Editing an Excel Formula with assigned range names
When I deselected the boxes in the wood spreadsheet the range names went back to cell addresses, but if I re-applied the names they then stayed put.
In xl2010 it displays the Lotus compatibility for each sheet separately, and they were checked for the Wd-col sheet, but not the Data sheet.
Another difference from the behaviour you described is that, if the Lotus compatibility is selected, when you press F2 to edit a cell the range names change to addresses in the cell display, but stay as range names in the edit bar at the top of the sheet. If you don't change anything the range names remain when you press enter. If you enter edit mode by clicking on the cell the range names change to addresses, as you described.
So it looks like the problem is related to the Lotus compatibility setting, with an added bug in xl2007. I'd recommend an upgrade to 2010 anyway (if you can still get it), which has many other advantages over 2007.
I just checked in xl2013 (which I'm not so keen on), and it retains the range names in the edit bar both when you press F2, and double click, so if you are having this problem with a lot of files 2013 might be the way to go.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Editing an Excel Formula with assigned range names
The problem appears to be solved with the Lotus settings. The trick is that it is not a general setting that affects the operation for all spreadsheets using Excel. It is a specific setting that gets saved with each sheet for each workbook.
You made my day a little brighter. Thanks again.