funny problem with range names
 

This isn't a VBA question, but this behavior is messing up one of my macros.

I've run into a funny behavior involving range names.

Put 1,2,3 into cells A1:A3
select these cells and define a Name, myrange.
move to cell C1 and type =myrange, and press enter.

In the formula bar when C1 is the active cell, I see =myrange.

If I press F2 to edit the formula, I still have =myrange in the formula bar.

Except on some worksheets, in certain files of mine, when I press F2 =myrange changes to =A1:A3 in the formula bar.

What is it about some worksheets that causes this to happen??? I'm hoping someone knows the answer because I can't figure it out. It doesn't seem to be the file, but only certain worksheets.

When this condition happens, it messes up the copying of formulas from one cell to another, and also does funny things to recalculation.

If anyone wants to see this, download the following file, which contains just one sheet with what I've described above. Press F2 on cell C1 and you should see =A1:A3 in the formula bar. I copied this sheet from one of my files that exhibits this strange behavior. A virgin file does not do this.

www.xlrotor.com/book2.xls

I'm using excel 2002, but excel 2000 does it also.

Thanks,

Brian Murphy
Austin, Texas

funny problem with range names
 

Hi Brian,

I suspect its because you have Transition formula entry & evaluation
checked.


regardsCharles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

"Brian Murphy" wrote in message
...
This isn't a VBA question, but this behavior is messing up one of my macros.

I've run into a funny behavior involving range names.

Put 1,2,3 into cells A1:A3
select these cells and define a Name, myrange.
move to cell C1 and type =myrange, and press enter.

In the formula bar when C1 is the active cell, I see =myrange.

If I press F2 to edit the formula, I still have =myrange in the formula bar.

Except on some worksheets, in certain files of mine, when I press F2
=myrange changes to =A1:A3 in the formula bar.

What is it about some worksheets that causes this to happen??? I'm hoping
someone knows the answer because I can't figure it out. It doesn't seem to
be the file, but only certain worksheets.

When this condition happens, it messes up the copying of formulas from one
cell to another, and also does funny things to recalculation.

If anyone wants to see this, download the following file, which contains
just one sheet with what I've described above. Press F2 on cell C1 and you
should see =A1:A3 in the formula bar. I copied this sheet from one of my
files that exhibits this strange behavior. A virgin file does not do this.

www.xlrotor.com/book2.xls

I'm using excel 2002, but excel 2000 does it also.

Thanks,

Brian Murphy
Austin, Texas

funny problem with range names
 

Charles, you're wonderful! You hit the nail right on the head. The culprit
seems to be the second check box in the Tools/Transition dialog called
Transition Formula Entry.

I should now be able to get my macro working like it should, or at least in
a more robust manner. It seems I can never do enough testing to ferret out
these things.

Thanks bunches,

Brian



"Charles Williams" wrote in message
...
Hi Brian,

I suspect its because you have Transition formula entry & evaluation
checked.


regardsCharles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

"Brian Murphy" wrote in message
...
This isn't a VBA question, but this behavior is messing up one of my
macros.

I've run into a funny behavior involving range names.

Put 1,2,3 into cells A1:A3
select these cells and define a Name, myrange.
move to cell C1 and type =myrange, and press enter.

In the formula bar when C1 is the active cell, I see =myrange.

If I press F2 to edit the formula, I still have =myrange in the formula
bar.

Except on some worksheets, in certain files of mine, when I press F2
=myrange changes to =A1:A3 in the formula bar.

What is it about some worksheets that causes this to happen??? I'm hoping
someone knows the answer because I can't figure it out. It doesn't seem
to
be the file, but only certain worksheets.

When this condition happens, it messes up the copying of formulas from one
cell to another, and also does funny things to recalculation.

If anyone wants to see this, download the following file, which contains
just one sheet with what I've described above. Press F2 on cell C1 and
you
should see =A1:A3 in the formula bar. I copied this sheet from one of my
files that exhibits this strange behavior. A virgin file does not do
this.

www.xlrotor.com/book2.xls

I'm using excel 2002, but excel 2000 does it also.

Thanks,

Brian Murphy
Austin, Texas