Home |
Search |
Today's Posts |
#1
|
|||
|
|||
write a function to determine if cells have names
I am doing a roster and would like to know how to do a function to make sure cells certain cells have names present eg. cells a1 : a5 should have names present - is there a function in say a6 that if all cells (a1:a5) have a name present a6 = true, and if any cell has no name present a6 = false (or any other text) I want this to make sure all cells are filled in with a name andrewm -- andrewm ------------------------------------------------------------------------ andrewm's Profile: http://www.excelforum.com/member.php...o&userid=23130 View this thread: http://www.excelforum.com/showthread...hreadid=377637 |
#2
|
|||
|
|||
On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
wrote: I am doing a roster and would like to know how to do a function to make sure cells certain cells have names present eg. cells a1 : a5 should have names present - is there a function in say a6 that if all cells (a1:a5) have a name present a6 = true, and if any cell has no name present a6 = false (or any other text) I want this to make sure all cells are filled in with a name andrewm If the cells can only contain names or be blank, something you can ensure using data validation, then =COUNTA(A1:A5) = 5 should do what you request. --ron |
#3
|
|||
|
|||
On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
wrote: I am doing a roster and would like to know how to do a function to make sure cells certain cells have names present eg. cells a1 : a5 should have names present - is there a function in say a6 that if all cells (a1:a5) have a name present a6 = true, and if any cell has no name present a6 = false (or any other text) I want this to make sure all cells are filled in with a name andrewm Correction: Data validation cannot "ensure" that a1:a5 contains names; but could be used to restrict possible entries to TEXT, and also to certain minimum length of text. For example, the validation formula: =and(ISTEXT(A1),len(a1)2) would restrict entries to be text and be at least three characters long. --ron |
#4
|
|||
|
|||
thanks, tried counta - started with false when no cells had names, changed to true when all cells had an entry, but when I deleted a cells contents the counta result stayed with true - any ideas andrewm -- andrewm ------------------------------------------------------------------------ andrewm's Profile: http://www.excelforum.com/member.php...o&userid=23130 View this thread: http://www.excelforum.com/showthread...hreadid=377637 |
#5
|
|||
|
|||
When you deleted the data did you use a space or something?
a lot of functions treat a space as an entry. i would recomend rather than Counta Counta counts functions concluding "" =if(countblank(A1:A5)=0,"true","false") "andrewm" wrote: thanks, tried counta - started with false when no cells had names, changed to true when all cells had an entry, but when I deleted a cells contents the counta result stayed with true - any ideas andrewm -- andrewm ------------------------------------------------------------------------ andrewm's Profile: http://www.excelforum.com/member.php...o&userid=23130 View this thread: http://www.excelforum.com/showthread...hreadid=377637 |
#6
|
|||
|
|||
On Thu, 9 Jun 2005 07:03:06 -0500, andrewm
wrote: thanks, tried counta - started with false when no cells had names, changed to true when all cells had an entry, but when I deleted a cells contents the counta result stayed with true - any ideas 1. Did you delete using the <delete key, or did you type in a <space? 2. Did you set up the data validation for A1:A5 as I suggested? --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using SUM function with #N/A in some cells | Excel Worksheet Functions | |||
clock | Excel Worksheet Functions | |||
Function to list values of last 3 non-blank cells in a vertical bl | Excel Worksheet Functions | |||
Determine cells that drive conditional formatting? | Excel Discussion (Misc queries) | |||
I cant use englisch function names in a swedich version of excel | Excel Discussion (Misc queries) |