Create a drop-down list without using data validation

Question

I have a shared workbook so I am unable to use data validation. Is there any other way of creating adrop-down list please?

Answer 1

Hello,

you can use data validation in a shared workbook, you just cannot set it up or change it while the workbook is shared. In order to set it up or change it, you have to unshare the workbook, apply the data validation, and then share the workbook again.

In general, shared workbooks are not a very good idea because they are unreliable and will become corrupt eventually. If you use shared workbooks, make sure that you have a very rigorous backup scheme, because the workbook can start acting funny and lose data any time. Shared workbooks are only good for tracking changes, but not for many people maintaining a single data source simultaneously. If you need multi-user edit access to a data set, please consider a database like Access or SQL.

cheers. teylyn

Answer 2

Yes. Advise all users to save their work and then exit the workbook. Then unshare it, apply the data validation and share it again.

From a change management perspective, you may want to send a message to all users that they need to save and close the file for maintenance work at a particular time, for example Wednesday at 4 pm. Of course, there's never a guarantee that all users will comply.

But that's user management. You need to make them understand that their changes may be lost unless they act on your request to save and exit.

Then take over the file, unshare, make changes and share again.

cheers, teylyn

Answer 3

Just another vote for moving to a relational database in such scenarios.  Excel was designed as a single-user application and even though Microsoft has tried to hammer it into a multi-user capable tool, they really haven't succeeded.  As others have said, it's not a matter of IF the file will become corrupt, but more of a WHEN will the file become corrupt situation.

Access should be a viable solution if the number of users is relatively small and the database is not all that large, but of all the relational database engines, it is probably the wimpiest.  If it seems a more robust relational database would be needed and cost becomes an issue for SQL Server, then you might look into MySQL - it's free, open source and very robust and it is ODBC compliant.

Answer 4

Thanks for all your help Teylyn.

Answer 5

Hi Teylyn,

When I unshare it I get an error message "...other users who are editing this workbook will not be able to save their changes...", presumably this is only if they are editing at the same time as I unshare it? Once I've shared it again, they will be able to save as before?

They are experiencing most of the problems you have mention and I have tried to get them to use Access but they won't budge.