Editing an Excel Formula with assigned range names 2

[SteveGregory]

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?

 

[IRstuff]

I don't seem to have a problem with that, but I don't see any obvious settings that would account for that.

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529

Of course I can. I can do anything. I can do absolutely anything. I'm an expert!

 

[IDS]

Try:

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/

 

[SteveGregory]

I can position the cursor at each cell reference in the formula and press F4 to change the cell reference back to a range name. However, the next time I start to edit the formula, the range names revert to cell references as soon as I place the cursor in the formula bar and I have to start all over again with the F4 key.

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???

 

[IRstuff]

Can you post a copy of the spreadsheet?

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529

Of course I can. I can do anything. I can do absolutely anything. I'm an expert!

 

[SteveGregory]

I checked several spreadsheets for this behavior. Some retain the range names and some do not.

Here is one that does not keep the range names when editing.

 

[SteveGregory]

Here is one of my spreadsheets that keeps the range names intact.

 

[IDS]

Steve - on my machine (running Xl2010) your wood spreadsheet did have the Lotus compatibility boxes checked. and the steel spreadsheet didn't.

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/

 

[SteveGregory]

Thanks Everyone.

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.