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