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

On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
<[email protected]> 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

On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
<[email protected]> 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

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

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
>
>

On Thu, 9 Jun 2005 07:03:06 -0500, andrewm
<[email protected]> 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

On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
<[email protected]> 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

On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
<[email protected]> 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

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
>
>

On Thu, 9 Jun 2005 07:03:06 -0500, andrewm
<[email protected]> 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

On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
<[email protected]> 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

On Thu, 9 Jun 2005 07:03:06 -0500, andrewm
<[email protected]> 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

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
>
>

On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
<[email protected]> 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

On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
<[email protected]> 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

On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
<[email protected]> 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

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
>
>

On Thu, 9 Jun 2005 07:03:06 -0500, andrewm
<[email protected]> 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

On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
<[email protected]> 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

On Thu, 9 Jun 2005 07:03:06 -0500, andrewm
<[email protected]> 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

On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
<[email protected]> 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

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
>
>

On Thu, 9 Jun 2005 07:03:06 -0500, andrewm
<[email protected]> 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

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
>
>

On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
<[email protected]> 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

On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
<[email protected]> 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

On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
<[email protected]> 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

On Thu, 9 Jun 2005 07:03:06 -0500, andrewm
<[email protected]> 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

On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
<[email protected]> 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

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
>
>

On Thu, 9 Jun 2005 07:03:06 -0500, andrewm
<[email protected]> 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

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
>
>

On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
<[email protected]> 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

On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
<[email protected]> 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

On Thu, 9 Jun 2005 07:03:06 -0500, andrewm
<[email protected]> 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

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
>
>

On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
<[email protected]> 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

On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
<[email protected]> 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

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
>
>

On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
<[email protected]> 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

On Thu, 9 Jun 2005 03:40:04 -0500, andrewm
<[email protected]> 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

On Thu, 9 Jun 2005 07:03:06 -0500, andrewm
<[email protected]> 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