Excel drop down list in another workbook

Question

I'm trying to have a list on one workbook and have another workbook get the list. All the videos I've seen reference "Name Manager" but there is no name manager in Excel 365 for Mac

I've tried a pivot table, data validation (this gives me an error message "This type of reference cannot be used in a Data Validation formula").

Answer 1

How I got the Initials Name Range:

How I created the Reference to the Master List:

Then when I go to Validate the Data, I get this:

Answer 2

Hi David,

So, it seems you are trying to use Data validation from a name range from MasterList workbook.

Please do steps below to see the result:

1. In your “IFT-R Course Training Group XXX” workbook, create a new name range (new name range in this current workbook, please notice this is not creating data validation)
2. Set this new name range name, for example “MasterListInitals”
3. Set the name range value to “=MasterList.xlsx!Instlnit”
4. Create data validation, set source to “=MasterListInitials”.

Regards,

Rena

Answer 3

That's exactly what I did. I still get the error message.

Or I get this:

Answer 4

Hi David,

Do not do something in your second screenshot. The error message "This type of reference cannot be used in a Data Validation formula" is because: you cannot use external workbook name range in Data Validation Source value.

In your first screenshot, you are doing correct to use a defined name. But you still meet error “A named range you specified cannot be found”. This error message will appear when: it cannot find the related defined name in the IFT-R Course Training Group XXX workbook.

Please make sure you:

In your “IFT-R Course Training Group XXX” workbook, create a new name range, set this range name to “MasterListInitals”, set the name range value to “=MasterList.xlsx!Instlnit”

Regards,

Rena

Answer 5

I'm sorry. I still don't understand what I'm doing wrong. I select a cell in “IFT-R Course Training ****” where I want the drop down, then Define Name in that cell as “MasterListInitals”, set the name range value to “=MasterList.xlsx!Instlnit”. I've done all that and when I do that I still get the error.

I'm really trying. I know I am not doing something correct, but I've followed your directions exactly and still can't get it to work.