• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation - How to have one shared validation list file for multiple files

P

Pooja

New Member
Hi,

I have multiple files with same strcuture, design, validations but used by different set of people. This each file I has same validation lists used. Now the problem is when I want to add new item in the validiation list I have to make changes in each and every file.

So is there are way to have one excel file with all the data validations in it and i can use the same refernce to other files in which the validation is required.
 
Hi Pooja ,

Refer this link :

http://www.contextures.com/xlDataVal05.html

The key point is this :

For data validation to work, the workbook which contains the list must be open, in the same instance of Excel, when you use the drop down lists. You could create the list in a workbook that is always open, but hidden, such as the Personal.xls workbook.
Click to expand...
Of course this will work only if the users are all working on the same computer , so that there is only one copy of the Personal.xls file.

Narayan
 
Hi Narayan,
thanks for the response. The location for all these files is on a shared path. I can create a Personal.xls file but will your solution still work (I mean if the location is shared space on particular server).
 
Hi Pooja ,

It will only work if all the users open Excel too from the shared server ; if each user is going to work on his / her own computer with their own copy of Excel , and use the shared server only to access the workbook , then this solution will not work.

Narayan
 
@Narayan,

Is it a good way to write a macro on user's workbook/s to update the validation list data whenever the data gets changed on the master workbook (on the network server)? user will have to click a button to get the latest data on their individual workbooks. This way we can make it work even if the user's workbooks are not on server as well.

Your expert opinion please? Thanks.
 
Hi Lohith ,

Not an expert opinion , but I think it should work ; only it's more work ; preferably instead of expecting the user to remember to click a button , you can use a Workbook_Open macro to open the Master List and update the data in the user's workbook.

Narayan
 
You must log in or register to reply here.
Back
Top