I'm going with the Dynamic Range function thanks! One more question
regarding the use of this validation list. How can I set this up so staff
can begin typing in the last name to locate an individual rather than having
to scroll through the entire list?

Mike

"T. Valko" wrote:

You can either use a dynamic range that automatically adjusts for the amount
of data you have:

http://contextures.com/xlNames01.html#Dynamic

Or, you can just make the referenced range big enough to cover any expected
amount of data. For example, you know that you never exceed 500 rows of data
so use R11:R500 as the range.

--
Biff
Microsoft Excel MVP


"watermt" wrote in message
...
I follow what you mean "if the referenced range does "not" change, but what
if I want the range to change as the imported data range changes?

The range change will most likely increases beyond R281 - with the ranges
starting cell remiaining the same at R11.

Mike

"T. Valko" wrote:

Probably the best bet would be...

....to see Biff's reply! <grin

--
Biff
Microsoft Excel MVP


"Bob Umlas" wrote in message
...
=SUMPRODUCT(N(NOT(ISERROR(FIND(9,R11:R281)))))
will work, but it will also count 9's found in 19, 29,793, etc.
You might toy with the formula and search for "9," but that would found
29,
or you might try " 9," but that would not find a leading 9 or just a 9.
So clearly, it's not trivial.
Probably the best bet would be a user-defined function.

Bob Umlas

"watermt" wrote in message
...
Using Excel 2003:
I'm trying to use the =COUNTIF function to count the number of
occurrences a
number appears (numbers 1 through 43) in cells within a column. Each
cell
can contain only one number or multiple numbers with no duplicates per
cell.

Here the formula:
=COUNTIF(Apr_09!R11:R281,"=9")

eg. cell R22 contains 2, 9, 34, 22, 10; cell R14 contains 1, 9, 33,
20,
15;
cell R30 contains 1, 9, 16, 27

I need a COUNT formula similar as above to look at the entire Range
(R11:R281) and count the number of time the number 9 appears in a
cell.
There can never be more than one instance of a number in each cell
(i.e.,
number 9 cannot appear in cell R30 more than once).

Any help is greatly appreciated,
Mike