Defined range problem

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

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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

Perhaps what you're after is a data validation droplist which will
skip any blanks: "" within the source range ?

If so, try this play ..

Assuming the source range is: $C$77:$C$2000

Use 2 helper columns, say, cols D and E?

Put in D77: =IF(C77="","",ROW())
Put in E77:
=INDEX($C$77:$C$2000,MATCH(SMALL($C$77:$C$2000,ROWS($A$1:A1)),$C$77:$C$2000,
0))

Select D77:E77, fill down to E2000

Use the formula below as a defined range
(say: MyList) for the data validation (DV):

[via Insert > Name > Define]
Names in workbook: MyList
Refers to:
=OFFSET(Centre!$E$77,0,0,SUMPRODUCT(--NOT(ISERROR(Centre!$E$77:$E$2000))),)

What you'll get in the DV with:
Allow: List
Source: =MyList

is a droplist which will skip
any blanks: "" within the range C$77:$C$2000

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Pat" <[email protected]> wrote in message
news:e$#[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>

> Put in E77:
>
=INDEX($C$77:$C$2000,MATCH(SMALL($C$77:$C$2000,ROWS($A$1:A1)),$C$77:$C$2000,
> 0))

Sorry, a correction to the formula above:

Put in E77:
=INDEX($C$77:$C$2000,MATCH(SMALL($D$77:$D$2000,ROWS($A$1:A1)),$D$77:$D$2000,
0))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

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


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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

Max,
I am quite interested in your suggestion for using in another part of the
workbook. I have tried it out and it almost worked for me.
I say almost because when I deleted a cell in colC the result of the formula
in colE shifted up one cell instead of staying where it should be. What this
has meant is the values of colC and colE are nolonger on the same row. Maybe
this is meant to work this way because the dropdown list is working as you
intended it to do. One other thing I noticed is the error #NUM! on in some
cells at the bottom of colE where there is nothing to calculate in some
cells in colC.

Pat

"Max" <[email protected]> wrote in message
news:[email protected]...
>> Put in E77:
>>
> =INDEX($C$77:$C$2000,MATCH(SMALL($C$77:$C$2000,ROWS($A$1:A1)),$C$77:$C$2000,
>> 0))
>
> Sorry, a correction to the formula above:
>
> Put in E77:
> =INDEX($C$77:$C$2000,MATCH(SMALL($D$77:$D$2000,ROWS($A$1:A1)),$D$77:$D$2000,
> 0))
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>

"Pat" <[email protected]> wrote:
> Max,
> I am quite interested in your suggestion for using in another part of the
> workbook. I have tried it out and it almost worked for me.
> I say almost because when I deleted a cell in colC the result of the
formula
> in colE shifted up one cell instead of staying where it should be. What
this
> has meant is the values of colC and colE are nolonger on the same row.
> Maybe this is meant to work this way because the dropdown list
> is working as you intended it to do.

It'll all stay in sync if you delete the entire row instead of shifting up
one cell in col C <g>.

One alternative to always point to the same range of cells in col C
(C77:C2000) irrespective might be to use INDIRECT, for example
we could try instead:

In D77: =IF(INDIRECT("C"&ROW(A77))="","",ROW())

In E77:

=INDEX(INDIRECT("$C$77:$C$2000"),MATCH(SMALL($D$77:$D$2000,ROWS($A$1:A1)),$D
$77:$D$2000,0))

with D77:E77 copied down to E2000 as before

The above means that we always want to point only to those cells in the
range: C77:C2000 as the target source, so whatever gets shifted up before
C77 (e.g.: to C76, C75, etc from where they were formerly within C77:C2000)
will be "lost" from the DV.

> One other thing I noticed is the error #NUM! on in some
> cells at the bottom of colE where there is nothing to calculate in some
> cells in colC.

The error cells are used / counted by the SUMPRODUCT in the defined range
formula to compute the number of items to show in the droplist (in a
converse manner, so as to speak)

Should the appearance of the error cells bother you, you could conditionally
format the range E77:E2000 with the formula: =ISERROR(E77), and choose a
font color to blend-in with the fill color (white?)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----