ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Preventing user entering duplicate values in a cell range (https://www.excelbanter.com/excel-worksheet-functions/58068-preventing-user-entering-duplicate-values-cell-range.html)

Thomas Peters

Preventing user entering duplicate values in a cell range
 
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 1s


Thanks,
itechxxiv


vezerid

Preventing user entering duplicate values in a cell range
 
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



All times are GMT +1. The time now is 08:18 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com