Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
andrewm
 
Posts: n/a
Default 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   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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   Report Post  
andrewm
 
Posts: n/a
Default


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   Report Post  
bj
 
Posts: n/a
Default

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   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using SUM function with #N/A in some cells Jeeper Excel Worksheet Functions 5 June 10th 05 06:28 AM
clock Wildman Excel Worksheet Functions 2 April 26th 05 10:31 AM
Function to list values of last 3 non-blank cells in a vertical bl ANJ Excel Worksheet Functions 1 April 14th 05 12:53 AM
Determine cells that drive conditional formatting? Nicolle K. Excel Discussion (Misc queries) 2 January 7th 05 02:08 AM
I cant use englisch function names in a swedich version of excel PE Excel Discussion (Misc queries) 2 December 7th 04 02:00 AM


All times are GMT +1. The time now is 01:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"