monitoring a changing range of cells

[Guest]

Hi,
I am trying to find/create a function which allows me to monitor a changing
range of cells. I have a column in which the user types yes or no, and then
in another cell I am checking to see if all of the cells say "yes". My
problem is that I want the user to be able to add more rows of yes or no to
the column and to be able to check these new cells for the "yes or no"
condition. I am currently using function code that looks like this.

=IF(((AND(EXACT("YES",UPPER(range)))))=TRUE,"YES","NO")

As you can see I have to give the function a defined range. I was thinking
of using a cell background colour checking function (the cells are
conditionally formatted to change colour when yes or no are typed in) that I
found on this page

http://cpearson.com/excel/colors.htm

to determine the range of cells to be checked, but I cannot get the
"RangeofColor" function to work, all I get is #value.

If anyone can help me out that would be greatly appriciated, and if anyone
has a simpler method of doing this I would love to hear it.

 

[Guest]

You could try a dynamic range:

In my test, I entered Yes/No values from cell A1 down through varying
numbers of cells and this formula returned the number of blank and non-yes
cells:

=SUMPRODUCT(--(OFFSET($A$1,,,SUMPRODUCT(MAX(((A1:A1000)<>"")*ROW(A1:A1000))))<>"yes"))

(you'd need to change the range references to suit your situation)

Does that help?