Can you create a LIST and DATA VALIDATION CRITERIA in same cell?

J

Janet

I have 7 cells in a column. In each cell in the range, I want the only
choice for entry to be TRUE. (If the statement isn't TRUE they will leave
blank.) I also want to specify Data Validation Criteria and generate an
error message so that if a user enters TRUE in more than 1 of the cells in
the range, an error message will be generated telling the user they can only
have True in only 1 of the 7 cells in the range. Can this be done and if so,
how?
 
B

Biff

Hi!

Do you mean that you have a drop down list in each of these 7 cells and the
only selection available is TRUE ?

If so, see if this is to you liking:

Assume the range of cells is A1:A7

Enter TRUE in some cell, say, J1.

J1 = TRUE

Select the range A1:A7
Goto Data>Validation
Allow: List
Source:

=IF(COUNTIF(A$1:A$7,TRUE)=0,J$1)

Select the Input Message tab

Enter a message something like this:

You may only select TRUE once in these cells

OK out.

Biff
 
Top