Hello,
I seem to be sending myself in circles with this one.
I have potentially 400 separate workbooks that are used to input data in. Primarily using dropdowns from data validation lists.
There are 7 different validation lists with varying rows.
When something new needs to be added to one of the lists, manually updating it, is just not viable.
Is there a good method for storing the lists in a single workbook that can be updated and then the validation inside the separate input workbooks read from it.
Thought process so far;
MS Access - probably the most ideal for the job, but I am still learning it and do not know enough at this time to deal with the complexity of the project.
Standard connection linking - cannot get it to run correctly from a closed workbook as is known and it is not viable for there to be 400 people holding the master validation open.
Power Query - doable, managed to read the validation workbook on open and grab the data. However due to the differing lengths of the rows. It would require 7 different queries to run on open to populate, which isn't ideal. However this is the only working method so far.
Batch file - would be amazing to just push the updates to a sheet hidden in the input workbooks, however is a no go due to very high security.
VBA - certainly doable, however I am about the level of a script kiddie. I can edit things I see to suit my needs but am not very good at writing my own code at all.
So any suggestions, advice or support would be much appreciated.
Thanks.
I seem to be sending myself in circles with this one.
I have potentially 400 separate workbooks that are used to input data in. Primarily using dropdowns from data validation lists.
There are 7 different validation lists with varying rows.
When something new needs to be added to one of the lists, manually updating it, is just not viable.
Is there a good method for storing the lists in a single workbook that can be updated and then the validation inside the separate input workbooks read from it.
Thought process so far;
MS Access - probably the most ideal for the job, but I am still learning it and do not know enough at this time to deal with the complexity of the project.
Standard connection linking - cannot get it to run correctly from a closed workbook as is known and it is not viable for there to be 400 people holding the master validation open.
Power Query - doable, managed to read the validation workbook on open and grab the data. However due to the differing lengths of the rows. It would require 7 different queries to run on open to populate, which isn't ideal. However this is the only working method so far.
Batch file - would be amazing to just push the updates to a sheet hidden in the input workbooks, however is a no go due to very high security.
VBA - certainly doable, however I am about the level of a script kiddie. I can edit things I see to suit my needs but am not very good at writing my own code at all.
So any suggestions, advice or support would be much appreciated.
Thanks.
