| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Global name used in local version
Hi all,
I define a global scope name, It works fine for all the other worksheets except one. in the error worksheet, I can see the global name in the formula bar dropdwon, when I click it, I am lead to the right location on the right sheet, but when I use the name as a validation on a cell, I am lead to a location on this error worksheet but with the correct row and column number Clara -- thank you so much for your help |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Global name used in local version
I am a little unclear exactly what is going on on your sheet but here is a
general explanation of named reange scope. There are two kinds of named ranges (for the purpose of this dsicussion). Global named ranges are attached to the workbook and are accessable to any sheet in the book. A local named range is attached to a specific sheet and it applies only to that sheet. Where a global and a local named range share the same name the local named range takes precidence. So if on yoru error work sheet you have a named range that matches the global named range then it takes precidence which might explain your difficulties. If you do not use the name manager download to manage your named ranges it is well worth getting a copy... http://www.oaltd.co.uk/MVP/ When you use named ranges in VBA things get a shade more complicated as a worksheet must be specified before you can define a range so named ranges require being explicit with the sheet where the named range comes from... If I have missed the mark with your question just let me know... -- HTH... Jim Thomlinson "clara" wrote: Hi all, I define a global scope name, It works fine for all the other worksheets except one. in the error worksheet, I can see the global name in the formula bar dropdwon, when I click it, I am lead to the right location on the right sheet, but when I use the name as a validation on a cell, I am lead to a location on this error worksheet but with the correct row and column number Clara -- thank you so much for your help |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Global name used in local version
Hi Jim,
the following is my summary of the problem, there are many sheets in my workbook, two of them are relevant to the problem, there are some names defined on sheet A, and I make them all global, on sheet B, I use the names as the sources of validation. On other sheets I use the same way, they all works well. But on Sheet B, I can use the formularbar's dropdown to go to the defined names on sheet A, but when I use the name as a source of validation, the name can not lead me to the Sheet A, but instead it will lead me to the same range on the sheet B. I use the following Macro to delete all local names on sheet B. Sub Macro6() Dim oName As Name For Each oName In ActiveSheet.Names oName.Delete Next End Sub so, on sheet B there are only global names. Clara -- thank you so much for your help "Jim Thomlinson" wrote: I am a little unclear exactly what is going on on your sheet but here is a general explanation of named reange scope. There are two kinds of named ranges (for the purpose of this dsicussion). Global named ranges are attached to the workbook and are accessable to any sheet in the book. A local named range is attached to a specific sheet and it applies only to that sheet. Where a global and a local named range share the same name the local named range takes precidence. So if on yoru error work sheet you have a named range that matches the global named range then it takes precidence which might explain your difficulties. If you do not use the name manager download to manage your named ranges it is well worth getting a copy... http://www.oaltd.co.uk/MVP/ When you use named ranges in VBA things get a shade more complicated as a worksheet must be specified before you can define a range so named ranges require being explicit with the sheet where the named range comes from... If I have missed the mark with your question just let me know... -- HTH... Jim Thomlinson "clara" wrote: Hi all, I define a global scope name, It works fine for all the other worksheets except one. in the error worksheet, I can see the global name in the formula bar dropdwon, when I click it, I am lead to the right location on the right sheet, but when I use the name as a validation on a cell, I am lead to a location on this error worksheet but with the correct row and column number Clara -- thank you so much for your help |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Global name used in local version
What does the name manager addin show you about the named ranges in question.
-- HTH... Jim Thomlinson "clara" wrote: Hi Jim, the following is my summary of the problem, there are many sheets in my workbook, two of them are relevant to the problem, there are some names defined on sheet A, and I make them all global, on sheet B, I use the names as the sources of validation. On other sheets I use the same way, they all works well. But on Sheet B, I can use the formularbar's dropdown to go to the defined names on sheet A, but when I use the name as a source of validation, the name can not lead me to the Sheet A, but instead it will lead me to the same range on the sheet B. I use the following Macro to delete all local names on sheet B. Sub Macro6() Dim oName As Name For Each oName In ActiveSheet.Names oName.Delete Next End Sub so, on sheet B there are only global names. Clara -- thank you so much for your help "Jim Thomlinson" wrote: I am a little unclear exactly what is going on on your sheet but here is a general explanation of named reange scope. There are two kinds of named ranges (for the purpose of this dsicussion). Global named ranges are attached to the workbook and are accessable to any sheet in the book. A local named range is attached to a specific sheet and it applies only to that sheet. Where a global and a local named range share the same name the local named range takes precidence. So if on yoru error work sheet you have a named range that matches the global named range then it takes precidence which might explain your difficulties. If you do not use the name manager download to manage your named ranges it is well worth getting a copy... http://www.oaltd.co.uk/MVP/ When you use named ranges in VBA things get a shade more complicated as a worksheet must be specified before you can define a range so named ranges require being explicit with the sheet where the named range comes from... If I have missed the mark with your question just let me know... -- HTH... Jim Thomlinson "clara" wrote: Hi all, I define a global scope name, It works fine for all the other worksheets except one. in the error worksheet, I can see the global name in the formula bar dropdwon, when I click it, I am lead to the right location on the right sheet, but when I use the name as a validation on a cell, I am lead to a location on this error worksheet but with the correct row and column number Clara -- thank you so much for your help |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| User Defined Functions - local or global? | Excel Worksheet Functions | |||
| Help Required!!! Macro to load data from version 1 to version 2 | Excel Worksheet Functions | |||
| Convert Local Names to Global Names | Excel Worksheet Functions | |||
| Recover earlier version of excel sheet after new version saved? | Excel Discussion (Misc queries) | |||
| How can I update the version of Excel 2000 9.0 to version 10.0 | Excel Discussion (Misc queries) | |||
Linear Mode
