SteveGregory
Structural
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?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations cowski on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!
Editing an Excel Formula with assigned range names 2
- Thread starter SteveGregory
- Start date
- Status
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!
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!
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
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
- Thread starter
- #4
SteveGregory
Structural
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???
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???
-
1
- #5
- Thread starter
- #6
SteveGregory
Structural
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.
Here is one that does not keep the range names when editing.
- Thread starter
- #7
SteveGregory
Structural
Here is one of my spreadsheets that keeps the range names intact.
-
1
- #8
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
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
- Thread starter
- #9
SteveGregory
Structural
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.
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.
- Status
Similar threads