Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 he 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation List and VLookup are ackting strange | Excel Worksheet Functions | |||
CountIF cells are not empty | Excel Discussion (Misc queries) | |||
list validation using list validation... | Excel Worksheet Functions | |||
Data Validation List Option Affecting Other Cells? | Excel Worksheet Functions | |||
Data Validation List Option Affecting Other Cells? | Excel Worksheet Functions |