Global range name problem

[Andre Croteau]

A few months ago, I learned from these newsgroups about how a global range
name is created.

Insert Range Name Define RANGE1 refers to =!$A$1:$A$10 (no mention
of the sheet name)

Now here is my problem:

Say I have in Sheet1 the value 5 in each of the cells A1 to A10
In B1 I type =sum(RANGE1) and I get the result 50. Thats fine.

Now if I enter the value 10 in cell A10, I would expect the total in cell B1
to change to 55, but it doesn't. It stays at 50 until I highlight B1 and
choose Edit (F2) and Enter, then I get 55. Even if I copy the formula unto
itself does not change the value, nor does pressing F9 to recalc the
worksheet.

Am I doing anything wrong, or is there a bug there somewhere? The global
range name is great for navigation or validation purposes, but I wonder
about being used for calculation.

Also, in another spreadsheet, I remember once seeing in the "Define Name"
window some sheet references to the right of the defined names, but don't
know how it gets there, or how to remove it.

Any help would be greatly appreciated.

Thank you

André

 

[Frank Kabel]

Hi
have you checked 'tools - Options - Calculate' that automatic
calculation is enabled?

 

[Myrna Larson]

It updates properly for me under automatic calculation and when I press F9
with calculation set to manual. Have you tried this in a different workbook?
Maybe the current one is corrupt.

BTW, what version of Excel are you using?

 

[Andre Croteau]

Hi Frank,

yes, my calculation is on "automatic".
and even when I press F9 to recalculate the sheet, the result is not changed

André

 

[keepITcool]

Be VERY carefull with these "Relative sheet reference" names.

See following page for details.
http://www.jkp-ads.com/ExcelNames.htm

Jan Karel Pieterse recommends following workaround:
MyRelativeName= Indirect("A1:A50")



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Andre Croteau wrote :

 

[Andre Croteau]

Hi Again,

Myrna, I am working with Excel 2000 on Windows XP.
I just shut down my computer and rebooted, and I still have the same
problem.
I asked a friend of mine to try it on his computer, and it worked well for
him...

I tried the other solution from keepITcool, and it worked well, so I guess
I'll be using that solution.
I still would love to know what causes the original version not to work...

thanks for your help