Convert Local Names to Global Names

[Leo Mansi]

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]

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]

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]

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.