CountA function - Data Validation
 

Hi

I have used the countA function in data validation in order that, from
a range of cells only one option can be slected using "x". If they try
to select more than one option an error meeasge is displayed. This works
fine, however I now need to use this same formula on a subsequent range
which contains empty string "". Obviously CountA counts this therefore
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;)


--
lauraroebuck
------------------------------------------------------------------------
lauraroebuck's Profile: http://www.thecodecage.com/forumz/member.php?userid=289
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122629

CountA function - Data Validation
 

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


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122629

CountA function - Data Validation
 

NBVC;442155 Wrote:
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 between
" " as didn't work fist time round but really appreciate your speedy
repsonse...wish I asked 2 hours ago instead of trying to work it out
(unsuccessfully) for myself!!

Thanks

Laura:BgrBg:)


--
lauraroebuck
------------------------------------------------------------------------
lauraroebuck's Profile: http://www.thecodecage.com/forumz/member.php?userid=289
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122629

CountA function - Data Validation
 

lauraroebuck;442160 Wrote:
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 speedy
repsonse...wish I asked 2 hours ago instead of trying to work it out
(unsuccessfully) for myself!!

Thanks

Laura:Bg:)

Glad we could be of help!


--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122629

CountA function - Data Validation
 

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

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"lauraroebuck" wrote:


Hi

I have used the countA function in data validation in order that, from
a range of cells only one option can be slected using "x". If they try
to select more than one option an error meeasge is displayed. This works
fine, however I now need to use this same formula on a subsequent range
which contains empty string "". Obviously CountA counts this therefore
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;)


--
lauraroebuck
------------------------------------------------------------------------
lauraroebuck's Profile: http://www.thecodecage.com/forumz/member.php?userid=289
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122629

CountA function - Data Validation
 

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

--
Biff
Microsoft Excel MVP


"NBVC" wrote in message
...

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


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=122629

[SOLVED]: CountA function - Data Validation
 

T. Valko;442685 Wrote:
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

--
Biff
Microsoft Excel MVP


"NBVC" wrote in message
...

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


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)

------------------------------------------------------------------------
NBVC's Profile: 'The Code Cage Forums - View Profile: NBVC'
(http://www.thecodecage.com/forumz/members/nbvc.html)
View this thread:
'[SOLVED]: CountA function - Data Validation - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=122629)


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


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122629