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

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?
--
J

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

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 DataValidation
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

"Janet" wrote in message
...
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?
--
J

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

In the Data Validation dialog box you want to select Custom in the
Allow list so that you can enter a formula.

The following formula

=NOT(AND(A1:A2))

will prevent someone from entering TRUE in both cells A1 and A2, but I
don't know if it's possible to do this for more than two cells at a
time. I tried using a seven-cell range, but that only validates to be
sure that not all seven cells are TRUE at the same time. I also tried
using curly braces to enter it as an array formula, but that didn't
help. Perhaps this reply will spark an idea in someone else.

Sorry I wasn't any more help at this time.

-Ryan


Janet wrote:
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?
--
J