Creating a large number of Defined Names that refer to cells in another workbook

[Pauleastgrinstead]

When in a workbook, there is "Create from Selection" under Name Manager that can quickly take a range of cells and create defined names without the need to manually enter them. I have created some 150 defined names in this way in workbook1 - mainly for use in dependent data dropdown lists.

When I am in workbook2 I want to create the same 150 defined names but refer to cells and cell ranges in workbook1 so the actual dropdown data is only defined in the one place. I can create the 150 names in the same way as I did in workbook1 but then I have to manually edit each name entry to refer to the worksheet in workbook2. Is there a way in Name Manager to do some sort of global change to change Sheet1! to [workbook1.xlsm]sheet1! for every defined name?

Is there a simple way that I have missed?

 

[Pauleastgrinstead]

I think that I will need to change to my dependent dropdown lists from using a defined names method to use tables. I have found a method at Create Dependent Drop-down Lists with Conditional Data Validation | Excel University. This will make it easy to point a workbook to tables that exist on another workbook.