Multiple cells, one of two values, no repetition

[Pr0]

Hi.
I'm having trouble figuring the formula for this one:

- column of 5 cells
- Data validation: list (Accepted/Rejected)

Multiple cells, one of two values, no repetition.jpg

There can only be one "Accepted" Cell - when the "Accepted" value is defined for a cell, all others have to change back to "Rejected"

Any help is appreciated.
Thank you.

PR

[JBeaucaire]

That's not a function, that would require VBA to CHANGE the current value in other cells.

However, you could get this without VBA as long as your willing to force people to set the previous ACCEPT back to REJECTED before than make a new ACCEPT choice.

You could simply use CONDITIONAL FORMATTING on all those cells to turn the RED if they say ACCEPT in more than one cell. Assuming those cells are B2:B6, a CF rule would look like this:

=AND(COUNTIF($B2:$B6, "Accepted")>1, $B2="Accepted")

That would cause just multiple ACCEPTED cells to light up as a warning...

[Pr0]

Hmm... is the VBA alternative complex to implement?
(I'm not a code savvy)
Thank you.

PR

[JBeaucaire]

Not at all, but unless you're using VBA already, this would require you to change your workbook to a .xlsm filetype. Not everyone likes/approves of VBA running in the background all the time. Tradeoffs.


The code itself is actually pretty simple. Again, assuming the range to watch is B2:B6, this macro would go into the sheet module:

1) right-click the sheet tab and select VIEW CODE
2) Paste in this code to the module that appears:

Please Login or Register  to view this content.

[Pr0]

Works like a charm!
Just one more thing - I do happen to have multiple tables (ranges) - how would the code look like then? I'm almost sure it's not a simple copy and paste...
Thanx again.

PR

[JBeaucaire]

Actually I built that with this issue in mind, just have each each "group separately" by editing the With..... range at the top of each section.

Please Login or Register  to view this content.

[Pr0]

Perfect!
Most grateful. :D
Thank you.

PR

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.