![]() |
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 |
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