one single value allowed in a column

[lcastanheiro]

I have a work sheet with several columns, one column has the position titles (column B) of my reports this is chosen from a drop down list.

[TABLE="width: 309"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD]yes[/TD]
[TD]Chief[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD]no[/TD]
[TD]Operation Specialist[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD]no[/TD]
[TD]Operation Specialist[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD]no[/TD]
[TD]Control Engineer[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD]no[/TD]
[TD]Startup Engineer
[/TD]
[/TR]
</tbody>[/TABLE]
What I want to do is read that column input, and if the value is "chief" then input into another column (column A) the value "YES". Then in this column only one "yes" will be allowed.
If i select Chief more than one time i should get an error.
This means there can only be one chief per project but I can have several engineers for example.
How can I do this?

Thanks for your help

 

[Jeffrey Mahoney]

You could just use a formula in Column A that results in Yes if column B has Chief in it. You could use Conditional formatting to show duplicates. The the user has an indication that they need to change the title of one of the Chiefs.

 

[Jeffrey Mahoney]

Cell A2: =if(B2="Chief","Yes","No")

Highlight all the titles in column B and create a conditional Formatting using Duplicates and choose your color.

 

[Fluff]

You can use a formula to prevent selecting Chief more than once.
With the DV list in say D1:D10 and the DV in B2 downwards
Select List & use this formula
=IF(COUNTIF(B$1:B1,"Chief")>0,D$2:D$10,D$1:D$10)

 

[lcastanheiro]

Cell A2: =if(B2="Chief","Yes","No")

Highlight all the titles in column B and create a conditional Formatting using Duplicates and choose your color.

Jeff, thanks. actually what i wanted to do was to alt the input and have the worksheet return an error window.

I had this working fine with the data validation. I highlighted the column A and in the DATA validation i entered the formula =COUNTIF($A:$A,$A$2)=1 then i changed it to =COUNTIF($O:$O,"yes")=1, because it was only looking if A2 = to yes. if A2 was actually "no" them it did not work.
Then i thought that column A inputs should be automatic and dependent on the value selected in column B. So I do not want to manually input yes or no in Column A. So I wrote the following in A2 and copied it down
=IF([@[StartUp assignment]]="chief","yes","no") where the dropdown list in B is name_range StartUp assignment].

When i did this the data validation stopped working.

 

[lcastanheiro]

Fluff, I am not understanding what you are saying. the values shown in B are imputed from a drop-down list which was created via the data validation option in the DATA tab and is based on table stored in another worksheet. so where exactly i am imputing the formula you suggest?

 

[Jeffrey Mahoney]

You can't use data validation to return the count of the item you enter. The value you enter has to match the criteria in the data validation.

I think what Fluff was trying to do was have Chief removed from the pulldown list as soon as it has been entered in the column.

Here's my version of that. This is a dynamic named range formula for the pulldown items. For this to work, "Chief" has to be at the top of the list. The name I gave it was "TitleList". In your data validation, put
=TitleList

Once you enter chief in the column, it's not available any longer to the other data validation cells.

=OFFSET(Sheet5!$H$1,COUNTIF(Sheet4!$E$2:$E$100,"Chief")+1,0,MATCH("zzzzzzzzzz",Sheet5!$H:$H)-ROW(Sheet5!$H$1)-COUNTIF(Sheet4!$E$2:$E$100,"Chief"),1)

You will have to replace the sheet names and the addresses in the formula to reflect what you have. The red addresses are the data validation range were you enter in the titles. The other addresses are for the location of the pulldown list items.

Jeff