Preventing user entering duplicate values in a cell range

[Thomas Peters]

I have a series of horizontal cells (7 IN ALL) that we use to track which
phases a document status is currently in.

A. not started=NS
B. in-progress=IP
C. under review=UR
D. reviewed=R
E. approved=A
F. on hold=OH
G. cancelled=C

We simply enter: "0"=NO or"1"=YES (Currently enter this manually and at
times fine that a document has to many "1"'s in a particluar row.

All phases will be populated with a "0" to begin with and the user will
enter/move "1" as the document progresses through each phase.

+---+-----+-----+-----+-----+-----+-----+-----+
| | A | B | C | D | E | F | G |
+---+-----+-----+-----+-----+-----+-----+-----+
| 1 | NS | IP | UR | R | A | OH | C |
+---+-----+-----+-----+-----+-----+-----+-----+
| 2 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
+---+-----+-----+-----+-----+-----+-----+-----+
| 3 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
+---+-----+-----+-----+-----+-----+-----+-----+
| 4 | 0 | [1] | 0 | 0 | [1] | 0 | 0 | <<I want to avoid
double enteries
+---+-----+-----+-----+-----+-----+-----+-----+
The brackets are merely to highlight the duplicates

To avoid the user entering duplicates “1†in a range of cells i.e. A4:G4. I
want to
incorporate a feature that will not allow the user to enter “1†in multiple
cells in the ROW.

For example: if the user enters a “1†in B4 (In-progress) and then returns
later to update the document phase and enters a “1†in C4 (Under Review)
cells A4, B4, D4, E4, F4, G4 will automatically change their cell value back
to “0†zero. And so on. So, regardless where you enter a “1†all the other
cells will change to “0†zero. Preventing a user from entering multiple “1â€â€™s


Thanks,
itechxxiv

 

[vezerid]

I suggest data validation. Use the Custom mode for column B and
onwards. Enter the formula:
=AND(OR(B2=1,B2=0), COUNTIF($A2:A2,1)=0)
You can select all cells from B2 until G_whatever to enter this at
once. You can also provide messages.
For column A:A just use the simpler formula
=OR(A2=1, A2=0)

HTH
Kostis Vezerides