Data Validation with Multiple Users/Computers

[wickedmosher]

So, I was trying to set up some Data Validation lists to prevent the wrong data from being entered onto a spread sheet. This spread sheet is used by multiple users and over multiple computers. We are using Excel 2007/2010. I set up all the data on my user log in on my PC, and when I tried to see if it works with other users and PC's, all the Data Validation was gone.

Questions...

1. When I set up Data Validation, does it stay tied to my user log in and PC?

2. Is their a way to get Data Validation on a spreadsheet working on multiple PC with multiple user log in's?

Any help that you can give will help me out, and as always thanks for the help.

 

[CWatts]

It should stay no matter what. I frequently use it to successfully share workbooks with multiple users that way when I have to run any analysis based on their entries, it's always using a fixed value.

What did you use for validation? Did you use a list or a custom formula of sorts? If you can provide that info, you'll probably get a real quick response.

 

[wickedmosher]

I used a list, and did not use any formulas. I used a simple list of color names on another tab on the worksheet. I do not have the sheet protected, so I have no need for sharing.

I though that setting up Data Validation would stay as a part of that worksheet no matter who was in it. I was just wondering if I had done something wrong setting it up, or if Excel keeps Data Validation exclusive to the computer that set it up.

Would it help if I put a copy of the worksheet up on my Box.net account and added a link on here?

 

[wickedmosher]

I started the excel sheet at work on Excel 2010. All the users that could not use the Data Validation were on Excel 2007.

I sent the file home and all the data validation I had added was working just fine.

Is their a compatibility issue with 2010 and 2007 for Data Validation?

 

[Peter_SSs]

Is their a compatibility issue with 2010 and 2007 for Data Validation?

In this circumstance, yes.

In previous versions (that is pre 2010) you could not use, directly, a range on another sheet for your DV list and that is your problem. However, it should be easy to overcome.

Go to you list in the other sheet and make the list a named range (eg DVlist) then in your data validation cells change the 'Source' box of the DV dialog from your range reference to
=DVlist

Save the workbook and then it should be fine when opened in 2007 or 2010.

 

[wickedmosher]

Thanks Peter, I will try that out tomorrow morning. I hope it works out. Thanks for the suggestion.

 

[hohenst]

Thank you Peter_SSs! It work perfectly for me!