Yes, No drop down menu that autopopulates

[Falinuszen]

I have created 5 cells that have drop down menu's that are simple yes no boxes. Each person is supposed to select the department they belong to thus changing the information they see and need. All of that is pretty easy. We have had some issues though with personnel not changing a yes to a no if someone else from another department has used the sheet before them. I cannot figure out how to set these five cells so that only one can have a yes in it, basically they go in select yes to their appropriate department and all others automatically change to no. Thank you in advance for your assistance.

[newdoverman]

Have you considered using Form Control, Option Buttons instead of a series of drop-down choices? With the Option buttons, all you have to do is to assign them to the same cell. The value in the cell changes to match the button clicked on. In addition, only one button can be selected leaving the others unselected. Your choice automatically changes the state of the unselected buttons and the selected cell for the outcome automatically enters a value that identifies the button selected.

Buttons.JPG

[Falinuszen]

Yes there are several things that would make it so much better lol but this is a sheet that was started years ago and would require hours to update the necessary formulas that refer to these five as "yes". I was hoping to save a day's worth of work in editing those formula's (5 changes to about 100 formulas)

[newdoverman]

You could use an IF formula to return "Yes" to the appropriate cell and No to the appropriate cells.

Something like this:
If the buttons returned a value to A1 and the yes/no values are in D1:D5
Enter in D1 and fill down.

Please Login or Register  to view this content.

This also works

Please Login or Register  to view this content.