CountA function - Data Validation

[lauraroebuck]

Hi

I have used the countA function in data validation in order that, fro
a range of cells only one option can be slected using "x". If they tr
to select more than one option an error meeasge is displayed. This work
fine, however I now need to use this same formula on a subsequent rang
which contains empty string "". Obviously CountA counts this therefor
no option can be selected.

Is there a way for the formula to ignore ""?

The formula I have used in Data, Validation, Custom is:

=COUNTA(G$5:G$7)=1

Please help!

Many thanks

Laura;

 

[NBVC]

Counta() doesn't count empty strings (nulls).. so should work as is.

If you mean blanks (as in space bar used in the cell) then try:

=COUNTA(G$5:G$7)-COUNTIF($G$5:$G$7," ")=1

 

[lauraroebuck]

Counta() doesn't count empty strings (nulls).. so should work as is.

If you mean blanks (as in space bar used in the cell) then try:

=COUNTA(G$5:G$7)-COUNTIF($G$5:$G$7," ")=1

Thank you so much this works, only had to get rid of the space betwee
" " as didn't work fist time round but really appreciate your speed
repsonse...wish I asked 2 hours ago instead of trying to work it ou
(unsuccessfully) for myself!!

Thanks

Laura:BgrBg:

 

[The Code Cage Team]

Thank you so much this works, only had to get rid of the space between
" as didn't work fist time round but really appreciate your speed
repsonse...wish I asked 2 hours ago instead of trying to work it ou
(unsuccessfully) for myself!!

Thanks

Laura:Bg

Glad we could be of help

--
The Code Cage Tea

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com

 

[Shane Devenshire]

If the value you are checking for is "x" then try this

=COUNTIF(G$5:G$7,"x")=1

 

[T. Valko]

Counta() doesn't count empty strings (nulls)

What do you consider to be an empty string?

A1: ="" (empty string)
A2: x
A3: <empty>

=COUNTA(A1:A3)

=2

 

[NBVC]

What do you consider to be an empty string?

A1: ="" (empty string)
A2: x
A3: <empty>

=COUNTA(A1:A3)

=2

I guess I meant as per A3... but looks like OP has A1