Contact US

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.

Students Click Here

Editing an Excel Formula with assigned range names

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! Already a Member? Login


Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

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