Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
[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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
COUNTA Function | Excel Worksheet Functions | |||
Counta function | Excel Worksheet Functions | |||
Using the COUNTA function | Excel Discussion (Misc queries) | |||
COUNTA Function not working =COUNTA(C3:C69,"NH") | Excel Worksheet Functions |