Excel data validation list from closed worksheet

0
votes

I have two Excel 2016 worksheets in the same folder. One is shipment form and the other master database for shipment history. This file also holds the list of "active customers", which obviously changes over time.

When user is creating a new shipment from the shipment form template, I want the customer to be selected from a drop-down list rather than filled in manually. While I'm able to set up standard data validation for a cell, the problem is that the list is maintained in a different Excel file (which, as stated, is in the same folder).

I was also able to set up name reference and have the list from the other spreadsheet available, however I still have one major usability obstacle - it only works when the user has opened the other file, I want to avoid that and just have the user work with shipment form template.

I.e. I'm looking for Excel to fetch the data validation list from another file without forcing the user to keep it open. What are my chances here?

Thanks!

1
vbaexcel
You could try to copy the "master list" to some cells in the existing sheet, and base the data validation drop-down list on those "intermediate" cells, rather than basing the data validation from the master list directly. - ImaginaryHuman072889
Yes, that's also something what crossed my mind, but to achieve good usability, I'd still need a functionality that performs this automatically and without manually needing to open the master file. Looks like if I use a simple formula like =[other_file.xlsx]Sheet1!A1 Then this will also work ONLY if this other worksheet is open at the same time, which doesn't really change much. Is there a more advanced option for fetching data from another Excel file w/o having it open? If the data is fresh, the validation list can obviously be based in the same file / workbook if need be. - dotsent12
Thanks again for re-inforcing the idea of using the intermediate cells. Looked more into it and looks like I can use "Get data" functionality to fetch the necessary list from the other workbook. - dotsent12
Not sure, perhaps it can be done with VBA but that's not really my area of expertise. I suggest adding the VBA tag to the question to attract some more people who might be able to help. - ImaginaryHuman072889
Will do so, essentially already got it to work, but a tad slow (usable though) - dotsent12

1 Answers

0
votes

You can use Power Query to get the data from the other file, even if it is closed. Power Query can get the list into the current spreadsheet and you can build the data validation on the Power Query result. You can configure the query to be refreshed when the file is opened, or the user can refresh it manually.