data validation with a formula

[LabrGuy Bob R]

Hello,
A1,A2,A3,A4,A5,A6,A7,A8,A9,A10 with a sum formula in A11.
I have 10 cells in a row and they may be left blank or they would have a 1
placed in one of them.
One of the 10 cells requires a number "1" to be entered for statistics.
There is conditional formatting to show one color if the total of the cells
=0.
If the total equals 1, then the conditional formatting doesn't display
If the total equals more than one, the conditional formatting will change to
another color.

My question is Is it possible to put a validation in A11 that if the
number exceeds 1 then a warning box will appear and not allow them to
continue until the offending number is corrected?

Or if the validation isn't the way to go, how can I ensure that if no
numbers are placed in A1:A10 where A11 would be 0, OR more than one 1 is
entered in A1:A10 and A11 shows a total greater than 1 to stop and not let
them go further so the error must be fixed.

Any help would be appreciated. Thank you, Bob Reynolds

 

[Bernard Liengme]

You cannot have a validation in cell A11 that test what is in cells A1:A10
Nor will Validation display a message when A11 has a formula
Are you prepared to consider a VBA solution?

BTW: belated Happy Canada Day to everyone (we got our independence much
later that the USA but celebrate earlier in the year!)

 

[Debra Dalgleish]

Select cells A1:A10
Choose Data>Validation
For Allow, choose Custom
In the Formula box, enter: =SUM($A$1:$A$10)<=1
Click OK

 

[Harlan Grove]

Hello,
A1,A2,A3,A4,A5,A6,A7,A8,A9,A10 with a sum formula in A11. ....
One of the 10 cells requires a number "1" to be entered for statistics. ....
My question is Is it possible to put a validation in A11 that if the
number exceeds 1 then a warning box will appear and not allow them to
continue until the offending number is corrected?

Or if the validation isn't the way to go, how can I ensure that if no
numbers are placed in A1:A10 where A11 would be 0, OR more than one 1 is
entered in A1:A10 and A11 shows a total greater than 1 to stop and not let
them go further so the error must be fixed.

Validation wouldn't apply to A11. Do you need A11 for anything else?

You could apply validation to each cell in A1:A10 by selecting A1:A10 and
using the custom validation formula

=AND(COUNTA($A$1:$A$10)=1,COUNTIF($A$1:$A$10,1)=1)

This won't prevent all cells being blank. There's no way to force entry
without using macros.

 

[BobR]

My thanks to all.
I need A11 as the total for A1-A10. I can't figure out what other purpose it
serves but I didn't design it.

Several mentions were made of code. I ultimately will get into code with a
copy and paste special portion of this project. Is the routine to do what I
want on every row difficult and can it be posted here or do I need to go to
programming?

Thanks
Bob Reynolds

 

[LabrGuy Bob R]

Both Debra and Harlan's suggestions work and I thank you very much.
One last question -- without using code (which I probably will have to) is
there a way to inform the user that they have not made any entry and an
entry is required in a1-a10 when they move out of column A?

I think the code will be the answer, but I have to give this a try first.
Thanks again
Bob

 

[Harlan Grove]

One last question -- without using code (which I probably will have to) is
there a way to inform the user that they have not made any entry and an
entry is required in a1-a10 when they move out of column A?

....

Not when they move out of A1:A10, but you could use a formula like

=IF(OR(COUNTA(A1:A10)<>1,COUNTIF(A1:A10,1)<>1),
"You must enter 1 in one and only one cell in A1:A10.","")

in some other cell close to A1:A10 as a prompt for your users.

 

[LabrGuy Bob R]

My thanks to all. We decided to put in code that when the document is saved
it will check then and see if any of those cells are blank, and then alert
the user at that time to go and fix it. I'm using the macro to alert in real
time to the fact that too many 11's are entered.

My thanks to all that responded.
BOB