#1   Report Post  
Posted to microsoft.public.excel.misc
Gee Gee is offline
external usenet poster
 
Posts: 32
Default Range Name Scope

I named my ranges using the "create from selection Tab" . I was not under the
impression that thisd defaults the scope of the name to the full workbook. I
have done many ranges this way and have used these in formulae. I need to
change the scope to the worksheet , and I dont want to have to redo the whole
sheet because of this .

How do I quickly change the scope of the range names ? I have tried it
through the Name manager but the scope is Greyed out.!!!

Please HELP ME anyone !!!!

GEE
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default Range Name Scope

1. Click [Review] tab.
2. If there is 'Unprotect Sheet' instead of 'Protect Sheet', Click
'Unprotect Sheet'
3. If 'Password' popup is displayed, enter the Password.
4. And check [Formulas]-[Name Manager].

Thanks.

"Gee" wrote:

I named my ranges using the "create from selection Tab" . I was not under the
impression that thisd defaults the scope of the name to the full workbook. I
have done many ranges this way and have used these in formulae. I need to
change the scope to the worksheet , and I dont want to have to redo the whole
sheet because of this .

How do I quickly change the scope of the range names ? I have tried it
through the Name manager but the scope is Greyed out.!!!

Please HELP ME anyone !!!!

GEE

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Range Name Scope

If you have to localize (or globalize) and existing name, you'll want to use Jan
Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager:
NameManager.Zip from http://www.oaltd.co.uk/mvp

In fact, if you're working with names, you'll want this!

Gee wrote:

I named my ranges using the "create from selection Tab" . I was not under the
impression that thisd defaults the scope of the name to the full workbook. I
have done many ranges this way and have used these in formulae. I need to
change the scope to the worksheet , and I dont want to have to redo the whole
sheet because of this .

How do I quickly change the scope of the range names ? I have tried it
through the Name manager but the scope is Greyed out.!!!

Please HELP ME anyone !!!!

GEE


--

Dave Peterson
  #4   Report Post  
Posted to microsoft.public.excel.misc
Gee Gee is offline
external usenet poster
 
Posts: 32
Default Range Name Scope + Pivot Charting

:
Thanks Dave , I did find the Name manager by looking for my subject on
previous threads , but thanks for the response. 3 Further questions if you
dont mind:

1. In the Name Manager by Karel Pieterse, one could only change 1 name at a
time else it came up with an error "list has been changed". Do u know if this
is correct or am I using it wrong. Yesterday I had to change over 300 names
one by one. Bit tedious.
2. The button Formula Create Selection , where the Names are adjacent to the
cell and you just select whioch side , a very useful feature , but it always
makes the names Global. Do you know if you can change the default to local
for this feature.
3. And here I am taking a chance :

Do you know of any addins , tutorials etc on Pivot Charting Techniques --
The Microsoft E-Learning does not cover this ,

Thanks and Regards
GEE

"Dave Peterson" wrote:

If you have to localize (or globalize) and existing name, you'll want to use Jan
Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager:
NameManager.Zip from http://www.oaltd.co.uk/mvp

In fact, if you're working with names, you'll want this!

Gee wrote:

I named my ranges using the "create from selection Tab" . I was not under the
impression that thisd defaults the scope of the name to the full workbook. I
have done many ranges this way and have used these in formulae. I need to
change the scope to the worksheet , and I dont want to have to redo the whole
sheet because of this .

How do I quickly change the scope of the range names ? I have tried it
through the Name manager but the scope is Greyed out.!!!

Please HELP ME anyone !!!!

GEE


--

Dave Peterson

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Range Name Scope + Pivot Charting

#1. I've never seen that message -- but I haven't used the name manager to do
what you're doing enough.

#2. I don't see a way using the built in menus. But you could create your own
macro that does it. You could make it as complex as you want or as simple as
you want.

This assumes that the ranges are on the sheet that gets the names (First column
has the name, second has the address):

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim TestRng As Range
Dim NewName As String

Set myRng = Selection.Areas(1).Columns(1)
For Each myCell In myRng.Cells
Set TestRng = Nothing
On Error Resume Next
Set TestRng = ActiveSheet.Range(myCell.Offset(0, 1).Value)
On Error GoTo 0

If TestRng Is Nothing Then
MsgBox "Failed as range with: " & myCell.Address(0, 0)
Else
NewName = "'" & ActiveSheet.Name & "'!" & myCell.Value
On Error Resume Next
TestRng.Name = NewName
If Err.Number < 0 Then
MsgBox "Failed as name with: " & myCell.Address(0, 0)
Err.Clear
End If
On Error GoTo 0
End If
Next myCell

End Sub

You could modify it so that you include the name. (I'd use a separate column to
make life easier.)

First column has the name, second has the address and third has the sheet name.

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim TestRng As Range
Dim NewName As String
Dim TestWks As Worksheet

Set myRng = Selection.Areas(1).Columns(1)
For Each myCell In myRng.Cells
Set TestWks = Nothing
On Error Resume Next
Set TestWks = Worksheets(myCell.Offset(0, 2).Value)
On Error GoTo 0

If TestWks Is Nothing Then
MsgBox "Failed as worksheet: " & myCell.Address(0, 0)
Else
Set TestRng = Nothing
On Error Resume Next
Set TestRng = TestWks.Range(myCell.Offset(0, 1).Value)
On Error GoTo 0

If TestRng Is Nothing Then
MsgBox "Failed as range with: " & myCell.Address(0, 0)
Else
NewName = "'" & TestWks.Name & "'!" & myCell.Value
On Error Resume Next
TestRng.Name = NewName
If Err.Number < 0 Then
MsgBox "Failed as name with: " & myCell.Address(0, 0)
Err.Clear
End If
On Error GoTo 0
End If
End If
Next myCell

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)


#3. Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx



Gee wrote:

:
Thanks Dave , I did find the Name manager by looking for my subject on
previous threads , but thanks for the response. 3 Further questions if you
dont mind:

1. In the Name Manager by Karel Pieterse, one could only change 1 name at a
time else it came up with an error "list has been changed". Do u know if this
is correct or am I using it wrong. Yesterday I had to change over 300 names
one by one. Bit tedious.
2. The button Formula Create Selection , where the Names are adjacent to the
cell and you just select whioch side , a very useful feature , but it always
makes the names Global. Do you know if you can change the default to local
for this feature.
3. And here I am taking a chance :

Do you know of any addins , tutorials etc on Pivot Charting Techniques --
The Microsoft E-Learning does not cover this ,

Thanks and Regards
GEE

"Dave Peterson" wrote:

If you have to localize (or globalize) and existing name, you'll want to use Jan
Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager:
NameManager.Zip from http://www.oaltd.co.uk/mvp

In fact, if you're working with names, you'll want this!

Gee wrote:

I named my ranges using the "create from selection Tab" . I was not under the
impression that thisd defaults the scope of the name to the full workbook. I
have done many ranges this way and have used these in formulae. I need to
change the scope to the worksheet , and I dont want to have to redo the whole
sheet because of this .

How do I quickly change the scope of the range names ? I have tried it
through the Name manager but the scope is Greyed out.!!!

Please HELP ME anyone !!!!

GEE


--

Dave Peterson


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I change a Name scope from local to Global? Henry Excel Discussion (Misc queries) 7 April 9th 10 10:36 PM
Change scope of a name hmm Excel Discussion (Misc queries) 0 October 30th 08 03:36 PM
Scope of Variables leerem Excel Discussion (Misc queries) 4 September 30th 08 12:54 PM
How can I change a Name scope from local to Global? Henry Excel Discussion (Misc queries) 0 September 23rd 08 07:24 PM
Scope of range names Printing of Range Name References Excel Discussion (Misc queries) 1 October 26th 05 09:37 PM


All times are GMT +1. The time now is 08:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"