Weird Range Name Issue

[Roncondor]

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

 

[AlphaFrog]

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]

I would need to see what you refer to. You can link your worksheet at box.net.

 

[Roncondor]

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

 

[Roncondor]

I would need to see what you refer to. You can link your worksheet at box.net.

To access this shared file, visit this link:
https://app.box.com/s/6ye785w07bgnu1kiip9z

 

[AlphaFrog]

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.

 

[Roncondor]

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]

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

 

[AlphaFrog]

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

You're welcome. I'm glad it worked out.

This is my default charity
water.org

 

[Roncondor]

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