That formula worked ok for me.

What does your formula look like?

Does it really return "" or does it return " " (a space character, maybe??)

If it does return a space character, I'd change that formula to return "".

But you could also trim the name formula:

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

Pat wrote:

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

--

Dave Peterson

Dave,
Well done, by trimming the formula you have sorted that one out perfectly.
Many thanks
Cheers
Pat


"Dave Peterson" wrote in message
...
That formula worked ok for me.

What does your formula look like?

Does it really return "" or does it return " " (a space character,
maybe??)

If it does return a space character, I'd change that formula to return "".

But you could also trim the name formula:

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

Pat wrote:

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

--

Dave Peterson