Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Local Names to Global Names
I am creating an application using Excel 2003. I have already created a
workbook that contains a lot of cells with data validation linked to lists which are all stored on a sheet named List Maintenance. Each of the individual lists is named with a range name. The validation settings refer to these range names and they all work fine in this workbook. There are going to be other workbooks that will need to use these same lists. So, I created a procedure to export the List Maintenance sheet to a separate file so that it can easily be imported into other workbooks as necessary. I then started working on the next workbook and created a procedure to import the List Maintenance sheet. But now the range names on that sheet have become Local names rather than Global names so I cannot refer to them with data validation, unless the data validation cells are on the same sheet, which they will not be. Is there a way to programmatically convert the Local names back to Global names once the sheet is imported? If not, do you have any recommendations on how to proceed? Thanks for your time. Leo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Local Names to Global Names
Get Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager: You can find it at: NameManager.Zip from http://www.oaltd.co.uk/mvp This is one of the features of this addin. Leo Mansi wrote: I am creating an application using Excel 2003. I have already created a workbook that contains a lot of cells with data validation linked to lists which are all stored on a sheet named List Maintenance. Each of the individual lists is named with a range name. The validation settings refer to these range names and they all work fine in this workbook. There are going to be other workbooks that will need to use these same lists. So, I created a procedure to export the List Maintenance sheet to a separate file so that it can easily be imported into other workbooks as necessary. I then started working on the next workbook and created a procedure to import the List Maintenance sheet. But now the range names on that sheet have become Local names rather than Global names so I cannot refer to them with data validation, unless the data validation cells are on the same sheet, which they will not be. Is there a way to programmatically convert the Local names back to Global names once the sheet is imported? If not, do you have any recommendations on how to proceed? Thanks for your time. Leo -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Local Names to Global Names
Thanks for the information, Dave!
Is there a way to do it without using the utitily you mentioned? I am developing this set of workbooks for a customer and they will be using it on multiple machines. Leo "Dave Peterson" wrote in message ... Get Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager: You can find it at: NameManager.Zip from http://www.oaltd.co.uk/mvp This is one of the features of this addin. Leo Mansi wrote: I am creating an application using Excel 2003. I have already created a workbook that contains a lot of cells with data validation linked to lists which are all stored on a sheet named List Maintenance. Each of the individual lists is named with a range name. The validation settings refer to these range names and they all work fine in this workbook. There are going to be other workbooks that will need to use these same lists. So, I created a procedure to export the List Maintenance sheet to a separate file so that it can easily be imported into other workbooks as necessary. I then started working on the next workbook and created a procedure to import the List Maintenance sheet. But now the range names on that sheet have become Local names rather than Global names so I cannot refer to them with data validation, unless the data validation cells are on the same sheet, which they will not be. Is there a way to programmatically convert the Local names back to Global names once the sheet is imported? If not, do you have any recommendations on how to proceed? Thanks for your time. Leo -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Local Names to Global Names
It's not hard creating the global names, but then you'll have to go through all
the places that names can be used to change them from local to global. That includes formulas in worksheets, occurrences in other names, the data|validation rules (like you wrote), conditional formatting and everything else you can think of. I think it would be more than a trivial excercise. Leo Mansi wrote: Thanks for the information, Dave! Is there a way to do it without using the utitily you mentioned? I am developing this set of workbooks for a customer and they will be using it on multiple machines. Leo "Dave Peterson" wrote in message ... Get Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager: You can find it at: NameManager.Zip from http://www.oaltd.co.uk/mvp This is one of the features of this addin. Leo Mansi wrote: I am creating an application using Excel 2003. I have already created a workbook that contains a lot of cells with data validation linked to lists which are all stored on a sheet named List Maintenance. Each of the individual lists is named with a range name. The validation settings refer to these range names and they all work fine in this workbook. There are going to be other workbooks that will need to use these same lists. So, I created a procedure to export the List Maintenance sheet to a separate file so that it can easily be imported into other workbooks as necessary. I then started working on the next workbook and created a procedure to import the List Maintenance sheet. But now the range names on that sheet have become Local names rather than Global names so I cannot refer to them with data validation, unless the data validation cells are on the same sheet, which they will not be. Is there a way to programmatically convert the Local names back to Global names once the sheet is imported? If not, do you have any recommendations on how to proceed? Thanks for your time. Leo -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy a worksheet and ensure range names stay local | Excel Programming | |||
How to translate excel function names from english to local langua | Excel Worksheet Functions | |||
Global Names Issue | Excel Programming | |||
Convert Local Names to Global Names | Excel Worksheet Functions | |||
Worksheet copy problem - local names | Excel Discussion (Misc queries) |