Validation Criteria & Ignore Blank (cells at bottom)

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
Fri May 09 22:12:42 CDT 2008

On May 10, 10:33 am, mjones <mich...@quality-computing.com> wrote:
> 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
Sat May 10 23:42:09 CDT 2008

On May 9, 11:12 pm, Ken Johnson <KenCJohn...@gmail.com> wrote:
> On May 10, 10:33 am, mjones <mich...@quality-computing.com> wrote:
>
>
>
> > 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

Hi Ken, This works perfectly. Thanks! Michele

Ken
Sat May 10 23:48:27 CDT 2008

On May 11, 2:42 pm, mjones <mich...@quality-computing.com> wrote:
> On May 9, 11:12 pm, Ken Johnson <KenCJohn...@gmail.com> wrote:
>
>
>
> > On May 10, 10:33 am, mjones <mich...@quality-computing.com> wrote:
>
> > > 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
>
> Hi Ken, This works perfectly. Thanks! Michele

Hi Michele,

You're welcome.

Ken Johnson