Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


Editing an Excel Formula with assigned range names

Editing an Excel Formula with assigned range names

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


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

RE: Editing an Excel Formula with assigned range names

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

RE: Editing an Excel Formula with assigned range names

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

RE: Editing an Excel Formula with assigned range names

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close