Validation Criteria & Ignore Blank (cells at bottom)

[mjones]

Hi All,

I have a named list that has a bunch of blank cells at the bottom so
the user can grow the list as needed. The ignore blank check box in
data validation looks tempting, but I still get all the blank cells at
the bottom of my drop down list. Does anyone know how to get it so
the blank cells don't show in the list.

Worksheet called License_Only has data from C2:C210. The data is
pulled from a second file as follows:

C2 - =IF(ISBLANK([Ontario_MGS_Price_List.xls]License_Only!C2),"",
[Ontario_MGS_Price_List.xls]License_Only!C2)
C3 - =IF(ISBLANK([Ontario_MGS_Price_List.xls]License_Only!C3),"",
[Ontario_MGS_Price_List.xls]License_Only!C3)

etc. to C252. Even thought C211 to C252 are blank, I'm hoping to have
the spreadsheet still work when new licenses are added to the bottom
of the price list in these blank cells.

Defined Name called License_Only is set to =OFFSET(License_Only!$C
$2,0,0,COUNTA(License_Only!$C:$C),1)

Worksheet called Definition has the drop down cell T39 with - Data
Validation > Settings: List, Ignore Blank checked, In-cell Dropdown
checked and Source is =License_Only.

I'm trying to get T39 to not include C211 to C252 (the blank cells
from the License_Only worksheet) unless there is data in them.

Any ideas?

Thanks!

Michele

 

[Ken Johnson]

Hi All,

I have a named list that has a bunch of blank cells at the bottom so
the user can grow the list as needed. The ignore blank check box in
data validation looks tempting, but I still get all the blank cells at
the bottom of my drop down list. Does anyone know how to get it so
the blank cells don't show in the list.

Worksheet called License_Only has data from C2:C210. The data is
pulled from a second file as follows:

C2 - =IF(ISBLANK([Ontario_MGS_Price_List.xls]License_Only!C2),"",
[Ontario_MGS_Price_List.xls]License_Only!C2)
C3 - =IF(ISBLANK([Ontario_MGS_Price_List.xls]License_Only!C3),"",
[Ontario_MGS_Price_List.xls]License_Only!C3)

etc. to C252. Even thought C211 to C252 are blank, I'm hoping to have
the spreadsheet still work when new licenses are added to the bottom
of the price list in these blank cells.

Defined Name called License_Only is set to =OFFSET(License_Only!$C
$2,0,0,COUNTA(License_Only!$C:$C),1)

Worksheet called Definition has the drop down cell T39 with - Data
Validation > Settings: List, Ignore Blank checked, In-cell Dropdown
checked and Source is =License_Only.

I'm trying to get T39 to not include C211 to C252 (the blank cells
from the License_Only worksheet) unless there is data in them.

Any ideas?

Thanks!

Michele

Hi Michelle,

COUNTA in =OFFSET(License_Only!$C$2,0,0,COUNTA(License_Only!$C:$C),1)
is the problem. It counts ""s resulting in your dynamic named range
including those unwanted cells in the data validation drop down.

You could try...

=OFFSET(License_Only!$C$2,0,0,SUMPRODUCT(--(License_Only!$C$1:$C
$252<>""),1)

instead, to define your dynamic named range.

Ken Johnson

 

[mjones]

Hi All,

I have a named list that has a bunch of blank cells at the bottom so
the user can grow the list as needed. The ignore blank check box in
data validation looks tempting, but I still get all the blank cells at
the bottom of my drop down list. Does anyone know how to get it so
the blank cells don't show in the list.

Worksheet called License_Only has data from C2:C210. The data is
pulled from a second file as follows:

C2 - =IF(ISBLANK([Ontario_MGS_Price_List.xls]License_Only!C2),"",
[Ontario_MGS_Price_List.xls]License_Only!C2)
C3 - =IF(ISBLANK([Ontario_MGS_Price_List.xls]License_Only!C3),"",
[Ontario_MGS_Price_List.xls]License_Only!C3)

etc. to C252. Even thought C211 to C252 are blank, I'm hoping to have
the spreadsheet still work when new licenses are added to the bottom
of the price list in these blank cells.

Defined Name called License_Only is set to =OFFSET(License_Only!$C
$2,0,0,COUNTA(License_Only!$C:$C),1)

Worksheet called Definition has the drop down cell T39 with - Data
Validation > Settings: List, Ignore Blank checked, In-cell Dropdown
checked and Source is =License_Only.

I'm trying to get T39 to not include C211 to C252 (the blank cells
from the License_Only worksheet) unless there is data in them.

Any ideas?

Michele

Hi Michelle,

COUNTA in =OFFSET(License_Only!$C$2,0,0,COUNTA(License_Only!$C:$C),1)
is the problem. It counts ""s resulting in your dynamic named range
including those unwanted cells in the data validation drop down.

You could try...

=OFFSET(License_Only!$C$2,0,0,SUMPRODUCT(--(License_Only!$C$1:$C
$252<>""),1)

instead, to define your dynamic named range.

Ken Johnson

Hi Ken, This works perfectly. Thanks! Michele

 

[Ken Johnson]

Hi All,
I have a named list that has a bunch of blank cells at the bottom so
the user can grow the list as needed. The ignore blank check box in
data validation looks tempting, but I still get all the blank cells at
the bottom of my drop down list. Does anyone know how to get it so
the blank cells don't show in the list.
Worksheet called License_Only has data from C2:C210. The data is
pulled from a second file as follows:
C2 - =IF(ISBLANK([Ontario_MGS_Price_List.xls]License_Only!C2),"",
[Ontario_MGS_Price_List.xls]License_Only!C2)
C3 - =IF(ISBLANK([Ontario_MGS_Price_List.xls]License_Only!C3),"",
[Ontario_MGS_Price_List.xls]License_Only!C3)
etc. to C252. Even thought C211 to C252 are blank, I'm hoping to have
the spreadsheet still work when new licenses are added to the bottom
of the price list in these blank cells.
Defined Name called License_Only is set to =OFFSET(License_Only!$C
$2,0,0,COUNTA(License_Only!$C:$C),1)
Worksheet called Definition has the drop down cell T39 with - Data
Validation > Settings: List, Ignore Blank checked, In-cell Dropdown
checked and Source is =License_Only.
I'm trying to get T39 to not include C211 to C252 (the blank cells
from the License_Only worksheet) unless there is data in them.
Any ideas?
Thanks!
Michele

Hi Michelle,

COUNTA in =OFFSET(License_Only!$C$2,0,0,COUNTA(License_Only!$C:$C),1)
is the problem. It counts ""s resulting in your dynamic named range
including those unwanted cells in the data validation drop down.

You could try...

instead, to define your dynamic named range.

Ken Johnson

Hi Ken, This works perfectly. Thanks! Michele

Hi Michele,

You're welcome.

Ken Johnson