Empty Cells in validation List

How do I get empty cells out of my validation list?

The Ignore Blank Cells function does not work.

Version: Excel 2000 Engelstalig

Did you include blank cells in the named range, so you could add more
dealer names later? If so, instead of leaving blank cells, you could
create a dynamic named range. There are instructions here:

http://www.contextures.com/xlNames01.html#Dynamic


Jasper wrote:
> How do I get empty cells out of my validation list?
>
> The Ignore Blank Cells function does not work.
>
> Version: Excel 2000 Engelstalig
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

I tried the approach and everything seems to work the way it should.


Inkoop =OFFSET(Inkoop Onderdelenlijst!$A$1:$A$100;0;0;COUNTA(Inkoop
Onderdelenlijst!$1:$1);1)
Maak =OFFSET(Maak Onderdelenlijst'!$A$1:$A$100;0;0;COUNTA(Maak
Onderdelenlijst'!$1:$1);1)
Sub ='Sub Onderdelenlijst'!$1:$65536100;0;0;COUNTA(Sub
Onderdelenlijst'!$1:$1);1)
MaKo =OFFSET(Sub Onderdelenlijst'!$A$1;0;0;COUNTA(Sub Inkoop
Onderdelenlijst'!$A:$A)-COUNTIF(Sub Onderdelenlijst'!$A:$A;0))

Untill the moment I fill in the

""IF(ROW()>COUNTA(Inkoop)+COUNTA(Maak);OFFSET(Sub;ROW()-1-COUNTA(Inkoop)-COUNTA(Maak);0;1;1);IF(ROW()>COUNTA(Inkoop);OFFSET(Maak;ROW()-1-COUNTA(Inkoop);0;1;1);OFFSET(Inkoop;ROW()-1;0;1;1)))"

Then Excel starts to ask ask me where the file of "Onderdelenijst" is. And
changes the "defined name Formulas"

Inkoop =OFFSET(Inkoop
[Onderdelenlijst]Onderdelenlijst!$A$1:$A$100;0;0;COUNTA(Inkoop
[Onderdelenlijst]Onderdelenlijst!$1:$1);1)
Maak =OFFSET(Maak '[Onderdelenlijst]Inkoop
Onderdelenlijst'!$A$1:$A$100;0;0;COUNTA(Maak '[Onderdelenlijst]Inkoop
Onderdelenlijst'!$1:$1);1)
Sub ='Sub Onderdelenlijst'!$1:$65536100;0;0;COUNTA(Sub
'[Onderdelenlijst]Inkoop Onderdelenlijst'!$1:$1);1)
MaKo =OFFSET(Sub '[Onderdelenlijst]Inkoop
Onderdelenlijst'!$A$1;0;0;COUNTA(Sub '[Onderdelenlijst]Inkoop
Onderdelenlijst'!$A:$A)-COUNTIF(Sub '[Onderdelenlijst]Inkoop
Onderdelenlijst'!$A:$A;0))

The Result is #NAME? Do you know what's wrong? I could send you the complete
file if you need it. Thanks,


"Debra Dalgleish" wrote:

Did you include blank cells in the named range, so you could add more dealer
names later? If so, instead of leaving blank cells, you could create a
dynamic named range. There are instructions here:

http://www.contextures.com/xlNames01.html#Dynamic

Jasper wrote:
How do I get empty cells out of my validation list? The Ignore Blank Cells
function does not work. Version: Excel 2000 english.