Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I change a Name scope from local to Global? | Excel Discussion (Misc queries) | |||
Change scope of a name | Excel Discussion (Misc queries) | |||
Scope of Variables | Excel Discussion (Misc queries) | |||
How can I change a Name scope from local to Global? | Excel Discussion (Misc queries) | |||
Scope of range names | Excel Discussion (Misc queries) |