Thank you for your help but I am afraid your formula still takes in
C808:C1000 which contain a formula but no data. If I extend the range of
your formula to say C1100 your formula will show the defined range to C1000.
So clearly the formula in C77:C1000 causes a problem in creating a defined
range.

Pat

"Dave Peterson" wrote in message
...
How about:

=OFFSET(Centre!$C$77,0,0,
MAX((Centre!$C$77:$C$1000<"")
*ROW(Centre!$C$77:$C$1000))-ROW(Centre!$C$77)+1,1)

If you know that your data won't exceed a certain number of rows, it's
less
taxing on excel when you limit the range. (I changed 65536 to 1000 in my
suggestion.)

==
By the way, =counta() counts formulas, too, no matter what they evaluate
to.

Pat wrote:

The following formula is used as a defined range:
=OFFSET(Centre!C$77,0,0,COUNTA(Centre!$C77:$C65536 ),1))

What I have difficulty with is that the formula does not ignore cells
that
contain a formula which do not contain any data.

Data ends in C807 but the formula continues on to C1000.
What change can be made to the defined range so that it ignores a
formula
and just recognise a value?

Hope someone will be able to point me the right direction.
Many thanks.
Pat

--

Dave Peterson