Weird Range Name Issue

Roncondor

Board Regular
Joined
Jun 3, 2011
Messages
58
I have used range names for over 10 years

However, I have a weird situation that I have not seen before and don’t seem to find on any postings anywhere on the internet.

I define a range name and two odd situations occur:
1. when I reference that range name in a formula, it becomes a RELATIVE reference NOT ABSOLUTE unless I put a “$” in front of the name. [This is how Lotus used range names].
2. When I go to edit a formula with the range name, the range name in the formula mysteriously disappears and the cell formula (e.g. “$A$1”) shows up instead.

I can send you a sample file showing the issue

The first/best solution wins a $50 donation to the non-denominational charity of your choice. (subject to mutual approval)

Thanks
Ron
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,382
What's the exact name used for the named range?
What's the refers to: for the named range?
Show an example of the formula both before and after the edit.
Does it happen for just the one named range or are there more (or all of your named ranges)?
 

madnes88

Board Regular
Joined
Nov 13, 2013
Messages
59
I would need to see what you refer to. You can link your worksheet at box.net.
 

Roncondor

Board Regular
Joined
Jun 3, 2011
Messages
58
What's the exact name used for the named range? c_cmbs_offset but it happens on many different ones as well
What's the refers to: for the named range? =Sheet1!$B$2
Show an example of the formula both before and after the edit. after the edit it reverts back to the rangename. During the edit [F2] is when it changes
Does it happen for just the one named range or are there more (or all of your named ranges)? It seems to happen on all of them on a certain worksheet but not all of the worksheets

I can send a sample via email
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,382
On the Excel Options dialog there is a checkbox to Transition Formula Entry which allows you to enter and evaluate formulas in a manner more consistent with the behavior of Lotus 1-2-3. In Excel 2003 it's on the Options\Transition tab. Not sure where it is on newer versions.

Is that option checked (or any of the other Transition options)?

What version of Excel do you have?

EDIT:
The Transitions options are checked in your example workbook. Uncheck them.
 
Last edited:

Roncondor

Board Regular
Joined
Jun 3, 2011
Messages
58

ADVERTISEMENT

Thank you! What charity do you want?

That was the problem - some of the worksheets were checked.

It is found in: Options - Advanced - Lotus Compatibility
 

Roncondor

Board Regular
Joined
Jun 3, 2011
Messages
58
On the Excel Options dialog there is a checkbox to Transition Formula Entry which allows you to enter and evaluate formulas in a manner more consistent with the behavior of Lotus 1-2-3. In Excel 2003 it's on the Options\Transition tab. Not sure where it is on newer versions.

Is that option checked (or any of the other Transition options)?

What version of Excel do you have?

EDIT:
The Transitions options are checked in your example workbook. Uncheck them.

Thank you! What charity do you want?

That was the problem - some of the worksheets were checked.

It is found in: Options - Advanced - Lotus Compatibility
 

Roncondor

Board Regular
Joined
Jun 3, 2011
Messages
58
You're welcome. I'm glad it worked out.

This is my default charity
water.org

Thank you for your gift in support of the work of Water.org.
Your gift will help to save lives and bring us closer to the day when everyone in the world can take a safe drink of water. Be assured, your gift will truly make a difference in the lives of others.
GIFT INFORMATION:
Amount: $50.00
Date: 12/26/2013
 

Watch MrExcel Video

Forum statistics

Threads
1,122,832
Messages
5,598,361
Members
414,233
Latest member
WolverineNurse

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top