A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Range Name Scope



 
 
Thread Tools Display Modes
  #1  
Old September 21st 09, 10:09 AM posted to microsoft.public.excel.misc
Gee
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
Ads
  #2  
Old September 21st 09, 12:30 PM posted to microsoft.public.excel.misc
Yvonne007
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  
Old September 21st 09, 01:13 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
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  
Old September 22nd 09, 07:03 AM posted to microsoft.public.excel.misc
Gee
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  
Old September 22nd 09, 01:32 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 02: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 02:07 PM.


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