Drop down updates with external spreadsheet for multiple users

[bradyg24]

We have many (about 100) spreadsheets shared with 4 users. They contain a dropdown with part numbers. The list of part numbers needs to be updated monthly with more options. We want to do this without having to update all the spreadsheets individually, but have them update when opened.

I saw that I could have the dropdown list pull from another data source spreadsheet that is already open, but I assume that all users would have to open both spreadsheets to see the updated data.

Is there another way to do this?

For what it is worth: domain environment, all users on-site, Windows 10 & 11, Office 365.

Many thanks!
Brady

[dosydos]

Multistep solution.
What i would do is create a MASTERLIST workbook that has all the parts in one tab in one column. I would then make this list into a TABLE.
Then on these 100 or so spreadsheets i would create a new tab. in cell A1 of the new tab i would use a FILTER function to link to the new table you created on the MASTERLIST workbook similar to below:

=FILTER(MASTERLIST!Table1[#Data],MASTERLIST!Table1[#Data]<>"")

This will create a Dynamic array and filter out any of the blanks. Then on the 100 or so worksheets i would make my data validation list link to these new tabs with the formula =$A$1#
It is imperative you add the hashtag at the end so that it will grab all the results from the dynamic array.

So when you have a new product, you would add it to the bottom of your table on the MASTERLIST workbook. When you open up any of the 100 or so sheets the FILTER function will automatically update to include the new data due to the table being updated, and this will automatically update the data validation list as well.

This process will be a pain in the *** to do 100 times, but once you complete all the new tab creations and linking you shouldnt have to update them ever again. So a lot of short term pain, but huge time saving in the long run.

[bradyg24]

Thank you for the detailed answer dosydos.

Would the MASTERLIST have to be running on a system that accesses one of the linking spreadsheets, or does it just need to be in a common location?

[dosydos]

Not entirely sure what you mean but I believe it just has to be on the same network. For example I could save the file MASTER file in any one of my drives on my companys network shown below, and the 100 sheets should still be able to link to it even if they are saved to any of the different drives. I would suggest testing this on your computer first. Shouldn't take long to make a mock parts list workbook and save it somewhere and then a test workbook in another location and try it to see if it works on your side.

[bradyg24]

I've done some testing with this with the MASTERLIST and the 'calling' spreadsheet in the same location and it seems to work well. If I want to have the MASTERLIST in a network location, say "\\server\share\MASTERLIST.XLSX," how would I adjust this code so that it is found:

=FILTER(MASTERLIST!Table1[#Data],MASTERLIST!Table1[#Data]<>"")

[bradyg24]

The one other thing I've found with this, is if the MASTERLIST isn't open, then the "calling" spreadsheet can't access the values. Is there any way around that?

[JeteMc]

Hello bradyg24 and Welcome to Excel Forum.
The following linked article describes accessing values from a closed workbook that is in a drive on a computer.
Perhaps it would work the same for a workbook in a network location.
https://exceloffthegrid.com/getting-...xcel-workbook/