Hi,
I have a list of values that I want to use in multiple files, preferably as data validation.
I searched for data-validation where the list lies in another workbook.
I found a solution requires the file with the list of values have to be open when using the data-validation, so this is not a real solution for me.
Is there a way to have a real solution Excel 365?
Regards,
Ayal Telem.
One way to achieve this would be to connect to the workbook containing the lists with Power Query and load them to a hidden worksheet in every file where you want to set-up data validation. Set the query(ies) to refresh upon opening.
Point your DV list to the table in the (hidden) sheet. Tested it myself with one list and it seems to work as expected. Update the 'master' list, save and close the workbook. Open the other file and you'll see that the DV list has been updated accordingly.
Hi Riny,
I thought about such a solution.
It is strange to me that MS didn't came up with a more elegant solution.
Thank you very much,
Ayal Telem.
