How do you create a list & data validation in same cell?
 

I have a range of 7 cells in a column. I want to be able to

a-allow only a "True" input (from a drop down list unless there is another
way to accomplish this)

b-if any of the other cells already contain "True", they can not duplicate
the entry in the other cells and display an error message that says they can
answer "True" only once in the range of cells.

How do you create a list & data validation in same cell?
 

Select the cells where you want the validation (B3:B9 in this example)
Choose Data Validation
From the Allow dropdown, choose Custom
In the Source box, type:
=OR(AND(COUNTIF($B$3:$B$9,TRUE)=1,B3=TRUE),B3="")
where B3 is a reference to the active cell

On the Error Alert tab, enter the message you want to appear if an
invalid entry is made
Click OK

JLS wrote:
I have a range of 7 cells in a column. I want to be able to

a-allow only a "True" input (from a drop down list unless there is another
way to accomplish this)

b-if any of the other cells already contain "True", they can not duplicate
the entry in the other cells and display an error message that says they can
answer "True" only once in the range of cells.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

How do you create a list & data validation in same cell?
 

I don't know of a way to PREVENT the second entry of "True" in another of the
seven cells. As you said you can use Data Validation to allow only "True" to
be entered. When you set this up, you want validation from a list, then in
the window below type 'True (single quote T-r-u-e) to prevent Excel from
treating "True" as a logical operator.

Now, in the cell below your seventh True cell, you could put in the formula:

=IF(COUNTA($A$2:$A$8)1,"You may enter a value of 'True' in ony one of the
highlighted cells above","")

(where A2-A8 are your seven cells), which will make a message appear if the
user enters a second "True". But it does not actually prevent them from
doing so.

Another way to do the error flag is to put this formula in the cell to the
right of the first of the seven cells, then copy it down to the 7th:

=IF(AND(COUNTA($A$2:$A$8)1,(A2="True")),"You may enter a value of 'True' in
ony one of the highlighted cells above","")

Using this, each of the cells with "True" get flagged, until the user
complies by deleting any extra entries.

HTH


"JLS" wrote:

I have a range of 7 cells in a column. I want to be able to

a-allow only a "True" input (from a drop down list unless there is another
way to accomplish this)

b-if any of the other cells already contain "True", they can not duplicate
the entry in the other cells and display an error message that says they can
answer "True" only once in the range of cells.