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,ROW S($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 <atyahoo<dotcom
----
"Pat" wrote in message
...
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