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

Hi David,

I find the cause based on further test. It seems we have to open the MasterList.xlsx at the same time. Or the MasterListInitals value “=MasterList.xlsx!Instlnit” cannot be recognized the related workbook location path.

Can you open the two workbooks at the same time and see the result?

Regards,

Rena

Answer 2

Yes. Both workbooks are open at the same time.

Answer 3

Hi David,

Is that convenient for you to provide me this two workbooks if they don’t have privacy information? It may be more easy for me to build the formula for you by my side and check the result.

To protect your privacy, I have sent a private message to you, you can upload the workbooks there. You can access the message via this link.

Regards,

Rena

Answer 4

Hi David,

Sorry for the late reply. It seems when this time I test them, the name range value needs to be changed to let it work. Please check the steps:

1. Delete existing name range Instlnit in MasterList.xlxs. Delete existing name range MasterListInitals in IFT-R Test 1.xlsx. We need to delete them so that the external link connection between the workbooks will be refreshed when you re-create them later. This can avoid potential external link connection problem.
2. Open MasterList.xlxs and IFT-R Test1.xlsx workbooks at the same time.
3. Go to MasterList.xlxs, create a new name range Instlnit. set the value to =Data!$B$2:$B$5. Save the changes.
4. Go to IFT-R Test 1.xlsx, create a new name range MasterListInstlnit, use your mouse select any cell in MasterList.xlxs, so it will show you =[MasterList.xlsx]Data!$B$2:$B$5, then change the part of the value to =[MasterList.xlsx]Data!Instlnit.
5. Then use data validation with value = MasterListInstlnit.

Note:

1. The two workbooks will create connection in this way. So, if we move the MasterList.xlxs file to another folder, the connection will not work anymore because it cannot find the file in original place.
2. We have to open the two workbooks at the same time to let the connection work.

Regards,

Rena

Answer 5

Hi Rena;

I followed all your directions and when I do the Data Validation, it returns this: MasterListInstlnit

You can see in the background that is the only thing listed on the dropdown list.