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

Hello David,

As you mentioned "Name Manger is not available in Excel 365 for Mac", I checked at my end in the latest version of Excel for Mac, where I can see the Name Manager in the Formulas tab when you open the Excel workbook.

So, I would like to confirm the screenshot of your Formulas tab and Excel version?

About "Data validation (this gives me an error message "This type of reference cannot be used in a Data Validation formula"). ", can you please try to share more detailed information for better understanding the situation?

Best Regards,

Chitrahaas

Answer 2

I have multiple gradebooks that I have numerous drop-down boxes.  I need to be able to pull the dropdown box from another workbook so I don't have to edit 45 workbooks when I need to change information. When I look for Name Manager, it is not under Formulas.

Answer 3

Hi David,

I can reproduce the same error message like below.

The possible cause should be, your Initials name range value is not referring to the correct value.

For example below, if I type the wrong value, the initials name range can still be created, but it cannot find the correct range based on the value “=NonExistingNameRange”. So it will give you error in the end in data validation feature.

You may need to make sure the initials name range value. Can you give me a screenshot about how you set the initials name range and its value?

Regards,

Rena

Answer 4

Hi David,

I’m not sure why you cannot see name manger button. But you only need Define Name button to do that. 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. Therefore,  you just need to create a new name range in the current workbook, and use this in data validation.

1. Create a defined name like below in your SourseData312.xlsx in “Data” Sheet. The Defined name in my screenshot is “Range1”. You can define the name as “Init” like you want. Then save the workbook.
2. Go to another workbook. Use Define Name button and define a new name in this workbook. In my screenshot, I set it to name “SourceRange” and set to “=RangeBook1.xlsx!Range1”. You can set name to any name like the same “SourceRange” and set Refers to  “=SourseData312.xlsx!Init” in your case.
3. Then go to data validation. Use the name range you just created for this current workbook.

 

Regards,

Rena

Answer 5

I followed you instructions exactly and got this message: