On Aug 13, 12:01 pm, Debra Dalgleish
wrote:
If the entries will all be text, define a name with this formula:

=OFFSET(Sheet1!$K$1,0,0,COUNTIF(Sheet1!$K:$K,"a") ,1)

and use that name as the data validation source.

If there will be numbers mixed in the external list, you can use Count.

=OFFSET(Sheet1!$B$1,0,0,COUNTIF(Sheet1!$B:$B,"a") +COUNT(Sheet1!$B:$B),1)

--
Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html- Hide quoted text -

- Show quoted text -

Thanks Debra this works perfectly.
=OFFSET(Sheet1!$K$1,0,0,COUNTIF(Sheet1!$K:$K,"a") ,1)
rather than my formula of =Lists!$K
$1:INDEX(CompetitorList,COUNTA(CompetitorList)) which only works IF
the data is local and the list has no formulas that return "".

Your formula gets away from the problem of Data Validation seeing the
cells with a zero string, "", as some sort of data value to display in
a drop list.

Now we can put lookup data and tables in a remote workbook but still
have a workaround for Data Validation to use mirrored data in a local
worksheet.

Would be nice if Data Validation in newer versions of Excel would be a
bit more friendly or Excel had a better way of dealing with true
NULLs.

Your help is much appreciated,
Dennis