PROBLEM: Data Validation(DV) dropdown shows blank choices.

QUESTION: How can I modify INDEX and COUNTA to ignore the "" values
in
a "CompetitorList" a named range list?


FORMULA: NameDefined RANGE for "CompetitorListRec"
=Lists!$K$1:INDEX(CompetitorList,COUNTA(CompetitorList))


If you don't have a quick answer Please read the explanation of the
issues below before telling me that there shouldn't be any "" in a
lookup list since this is a bit more complicated that it looks at
first. There is a good reason why I have lists with "" values in
them. It is just a lengthy explanation.


ISSUES:
Data Validation(DV) insists on lists in the same worksheet.
Named ranges CAN be used IF in the same workbook. However, Named
ranges of full columns cause blank records down to 65k to show in the
DV dropdown. DV is a bit lame but quite valuable for restricting
data
input.
In order to limit the blank spaces in the dropdown I originally
restricted the DV list length for the lookup by using the INDEX and
COUNTA settings to limit the validation list to cells with data:
For example, In a worksheet column I have Data Validation =
CompetitorListRec


NameDefined for "CompetitorListRec" =Lists!$K
$1:INDEX(CompetitorList,COUNTA(CompetitorList))


Where "Lists" is a local worksheet, "CompetitorList" is a named full
column range (colK).


In this case DV=CompetitorListRec defined just above will only show
records with a value in the named "CompetitorList" range column. No
blanks appear in the DV dropdown.
PERFECT. This works just fine WHEN the "Lists" worksheet is an
internal worksheet AND the only list cell values are contiguous
sorted
data with empty cells below. NO PROBLEMS.


The PROBLEM of "Data Validation dropdown shows blank choices" has now
resurfaced BECAUSE I moved all my lookup tables and lists to an
external workbook in order to facilitate updates to those tables.
(The
external workbook with a hidden and protected worksheet named
"DDLists" can then be easily sent to clients and placed in the same
folder as their templates. This works just fine for calculations in
the client template which now use the external workbook for it's
lookup lists and tables.) LOOKUP Table/List update problem solved.


HOWEVER, DV has reared it's ugly head again. DV insists on a local
internal worksheet named range.
In order to give DV a LOCAL sheet or local named range BUT really use
EXTERNAL workbook lists or tables I have come up with a workaround.
Continue to use a local worksheet named "DDLists" for the DV. This
local sheet however, does nothing except MIRROR some of the lists and
tables in the external workbook for DV.
I MIRROR the external values by just using simple formulas in the
local worksheet that obtain data from the external workbook such as
the following formula in internal worksheet "DDLists" Cell K5:
=IF([DDLists.xls]DDLists!K5<>"",[DDLists.xls]DDLists!K5,"")
So, if a value exists in the external workbook at K5 then the
internal
worksheet has the same value at its K5. So far so good.
I can NOW use the INTERNAL worksheet's named range to refer to colK
so
DV is happy even though the actual data resides OUTSIDE this
workbook.


Kludgy workaround DV but this works just fine.


Kludgy part is that I now have to guess at how many records will be
needed in the local list and copy the MIRROR formulas to empty cells
in that list. So if I see 50 records in the external workbook then I
copy the MIRROR formula down for 250 records just to be sure for a
while at least. Kludgy but works.


FINALLY THE REAL PROBLEM. Data Validation(DV) dropdown shows blank
choices.


I'm now back to the problem of DV seeing 250 records when there are
really only 50 with data and the other 200 records are formula values
that return "" in the local list so the DV dropdown has an extra 200
blank values again. Bummer.


USER ISSUE: The DV dropdown doesn't start its display from the top
but
by default visually displays all blank choices until you scroll up.
Most users find having to scroll up to see the choices very
confusing.


How can I modify the INDEX and COUNTA in =Lists!$K
$1:INDEX(CompetitorList,COUNTA(CompetitorList)) to ignore the ""
values in a list?


Thanks, Dennis

Suppose you have a list which, as the result of linking or calculation, may
have blanks in it.
For example A1 thru A30:
word1
word2




word7
word8
word9

word11


word14
word15
word16
word17

word19
word20

word22
word23
word24

word26

word28
word29
word30

We need to create another (dynamic) list which will be like the first,
except the blanks will be removed.

In B1 enter 1 and in b2 enter:

=IF(A2="",0,MAX($B$1:B1)+1) and copy down. We see:

word1 1
word2 2
0
0
0
0
word7 3
word8 4
word9 5
0
word11 6
0
0
word14 7
word15 8
word16 9
word17 10
0
word19 11
word20 12
0
word22 13
word23 14
word24 15
0
word26 16
0
word28 17
word29 18
word30 19

Note that blanks in column A are marked by zeros in column B. Finally in C1:

=INDEX($A$1:$A$30,MATCH(ROW(),B$1:B$30,0),1) and copy down till #N/A

We see:

word1 1 word1
word2 2 word2
0 word7
0 word8
0 word9
0 word11
word7 3 word14
word8 4 word15
word9 5 word16
0 word17
word11 6 word19
0 word20
0 word22
word14 7 word23
word15 8 word24
word16 9 word26
word17 10 word28
0 word29
word19 11 word30
word20 12
0
word22 13
word23 14
word24 15
0
word26 16
0
word28 17
word29 18
word30 19

Just use column C for the data validation list rather than column A.

--
Gary''s Student - gsnu200737


"ssGuru" wrote:

> PROBLEM: Data Validation(DV) dropdown shows blank choices.
>
> QUESTION: How can I modify INDEX and COUNTA to ignore the "" values
> in
> a "CompetitorList" a named range list?
>
>
> FORMULA: NameDefined RANGE for "CompetitorListRec"
> =Lists!$K$1:INDEX(CompetitorList,COUNTA(CompetitorList))
>
>
> If you don't have a quick answer Please read the explanation of the
> issues below before telling me that there shouldn't be any "" in a
> lookup list since this is a bit more complicated that it looks at
> first. There is a good reason why I have lists with "" values in
> them. It is just a lengthy explanation.
>
>
> ISSUES:
> Data Validation(DV) insists on lists in the same worksheet.
> Named ranges CAN be used IF in the same workbook. However, Named
> ranges of full columns cause blank records down to 65k to show in the
> DV dropdown. DV is a bit lame but quite valuable for restricting
> data
> input.
> In order to limit the blank spaces in the dropdown I originally
> restricted the DV list length for the lookup by using the INDEX and
> COUNTA settings to limit the validation list to cells with data:
> For example, In a worksheet column I have Data Validation =
> CompetitorListRec
>
>
> NameDefined for "CompetitorListRec" =Lists!$K
> $1:INDEX(CompetitorList,COUNTA(CompetitorList))
>
>
> Where "Lists" is a local worksheet, "CompetitorList" is a named full
> column range (colK).
>
>
> In this case DV=CompetitorListRec defined just above will only show
> records with a value in the named "CompetitorList" range column. No
> blanks appear in the DV dropdown.
> PERFECT. This works just fine WHEN the "Lists" worksheet is an
> internal worksheet AND the only list cell values are contiguous
> sorted
> data with empty cells below. NO PROBLEMS.
>
>
> The PROBLEM of "Data Validation dropdown shows blank choices" has now
> resurfaced BECAUSE I moved all my lookup tables and lists to an
> external workbook in order to facilitate updates to those tables.
> (The
> external workbook with a hidden and protected worksheet named
> "DDLists" can then be easily sent to clients and placed in the same
> folder as their templates. This works just fine for calculations in
> the client template which now use the external workbook for it's
> lookup lists and tables.) LOOKUP Table/List update problem solved.
>
>
> HOWEVER, DV has reared it's ugly head again. DV insists on a local
> internal worksheet named range.
> In order to give DV a LOCAL sheet or local named range BUT really use
> EXTERNAL workbook lists or tables I have come up with a workaround.
> Continue to use a local worksheet named "DDLists" for the DV. This
> local sheet however, does nothing except MIRROR some of the lists and
> tables in the external workbook for DV.
> I MIRROR the external values by just using simple formulas in the
> local worksheet that obtain data from the external workbook such as
> the following formula in internal worksheet "DDLists" Cell K5:
> =IF([DDLists.xls]DDLists!K5<>"",[DDLists.xls]DDLists!K5,"")
> So, if a value exists in the external workbook at K5 then the
> internal
> worksheet has the same value at its K5. So far so good.
> I can NOW use the INTERNAL worksheet's named range to refer to colK
> so
> DV is happy even though the actual data resides OUTSIDE this
> workbook.
>
>
> Kludgy workaround DV but this works just fine.
>
>
> Kludgy part is that I now have to guess at how many records will be
> needed in the local list and copy the MIRROR formulas to empty cells
> in that list. So if I see 50 records in the external workbook then I
> copy the MIRROR formula down for 250 records just to be sure for a
> while at least. Kludgy but works.
>
>
> FINALLY THE REAL PROBLEM. Data Validation(DV) dropdown shows blank
> choices.
>
>
> I'm now back to the problem of DV seeing 250 records when there are
> really only 50 with data and the other 200 records are formula values
> that return "" in the local list so the DV dropdown has an extra 200
> blank values again. Bummer.
>
>
> USER ISSUE: The DV dropdown doesn't start its display from the top
> but
> by default visually displays all blank choices until you scroll up.
> Most users find having to scroll up to see the choices very
> confusing.
>
>
> How can I modify the INDEX and COUNTA in =Lists!$K
> $1:INDEX(CompetitorList,COUNTA(CompetitorList)) to ignore the ""
> values in a list?
>
>
> Thanks, Dennis
>
>

In <F5991D55-2A8A-4106-816D-(E-Mail Removed)>, Gary''s
Student <(E-Mail Removed)> spake thusly:

> We need to create another (dynamic) list which will be like the
> first, except the blanks will be removed.
>
> In B1 enter 1 and in b2 enter:
>
> =IF(A2="",0,MAX($B$1:B1)+1) and copy down. We see:

Thanks for posting this, Gary's STudent. I found it
helpful to see the step-by-step.

=dman=

Thanks Garys Student who said:
> Suppose you have a list which, as the result of linking or calculation, may
> have blanks in it.
> For example A1 thru A30:
> word1
> word2
>
> word7
> word8

Basic problem Garys Student is that Data Validation will ONLY use a
local named range.

Maybe I didn't make my question clear enough. I DON'T have
intermittent blanks mixed in the presorted lookup table in the
EXTERNAL workbook. That is a no no and LOOKUP on such a table would
fail anyway. My external table column K, ONLY has data starting at
row2 after the header, NO formulas, and the table is automatically
sorted by code when new data is added.

Data Validation if set on this whole column would also shows blanks in
its dropdown. But, telling Data Validation to look to a subset of this
column that ONLY contains data makes it display only values in the
dropdown and no blanks. So far so good. This formula for a
restricted named range successfully does that. =Lists!$K
$1:INDEX(CompetitorList,COUNTA(CompetitorList)) WHEN USED LOCALLY IN
THE EXTERNAL WORKBOOK for local Data Validation.

My PROBLEM is that I am trying to apply that same logic to an internal
worksheet in another workbook where col K between the two workbooks is
mirrored into a local worksheet by the following If Then formulas and
THEN use this internal range column for my Data Validation to keep it
happy.

Cells in col K in the internal worksheet that visually appear as empty
actually have the formula =IF([DDLists.xls]DDLists!K5<>"",
[DDLists.xls]DDLists!K5,"") Data Validation works against this
column just fine but of course there are now cells with a "" rather
than just being NULL so it also displays all the blank cells in the
dropdown. Bummer.

I need to change EITHER of these two formulas to give Data Validation
a list that ONLY contains data and not "" or come up with a different
plan.

The mirror formula =IF([DDLists.xls]DDLists!K5<>"",
[DDLists.xls]DDLists!K5,"") could be changed to give a NULL value
instead of "" in some way. "0" won't work.

OR the formula below which defines a named range to stop counting when
it reaches a "" value in col K.
FORMULA: NameDefined RANGE for "CompetitorListRec"
=Lists!$K$1:INDEX(CompetitorList,COUNTA(CompetitorList))

Maybe change COUNTA(If(CompetitorList<>"",COUNTA, Don't count???

I can't use the external table INDEX to restrict the named range list
such as =Lists!$K
$1:INDEX([DDLists.xls]CompetitorList,COUNTA([DDLists.xls]CompetitorList))
because Data Validation DEMANDS an internal table. It is confusingly
quite happy thinking that data in a cell that has a formula that
results in getting data FROM an external table is an internal
worksheet with internal values.

Perhaps I can add code that checks the external table and when it
grows to add the mirror If Then formula to a corresponding cell in col
K in the internal worksheet. There must be a simpler way.

Thanks for any help or advice,
Dennis

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)


ssGuru wrote:
> Thanks Garys Student who said:
>
>>Suppose you have a list which, as the result of linking or calculation, may
>>have blanks in it.
>>For example A1 thru A30:
>>word1
>>word2
>>
>>word7
>>word8
>
>
> Basic problem Garys Student is that Data Validation will ONLY use a
> local named range.
>
> Maybe I didn't make my question clear enough. I DON'T have
> intermittent blanks mixed in the presorted lookup table in the
> EXTERNAL workbook. That is a no no and LOOKUP on such a table would
> fail anyway. My external table column K, ONLY has data starting at
> row2 after the header, NO formulas, and the table is automatically
> sorted by code when new data is added.
>
> Data Validation if set on this whole column would also shows blanks in
> its dropdown. But, telling Data Validation to look to a subset of this
> column that ONLY contains data makes it display only values in the
> dropdown and no blanks. So far so good. This formula for a
> restricted named range successfully does that. =Lists!$K
> $1:INDEX(CompetitorList,COUNTA(CompetitorList)) WHEN USED LOCALLY IN
> THE EXTERNAL WORKBOOK for local Data Validation.
>
> My PROBLEM is that I am trying to apply that same logic to an internal
> worksheet in another workbook where col K between the two workbooks is
> mirrored into a local worksheet by the following If Then formulas and
> THEN use this internal range column for my Data Validation to keep it
> happy.
>
> Cells in col K in the internal worksheet that visually appear as empty
> actually have the formula =IF([DDLists.xls]DDLists!K5<>"",
> [DDLists.xls]DDLists!K5,"") Data Validation works against this
> column just fine but of course there are now cells with a "" rather
> than just being NULL so it also displays all the blank cells in the
> dropdown. Bummer.
>
> I need to change EITHER of these two formulas to give Data Validation
> a list that ONLY contains data and not "" or come up with a different
> plan.
>
> The mirror formula =IF([DDLists.xls]DDLists!K5<>"",
> [DDLists.xls]DDLists!K5,"") could be changed to give a NULL value
> instead of "" in some way. "0" won't work.
>
> OR the formula below which defines a named range to stop counting when
> it reaches a "" value in col K.
> FORMULA: NameDefined RANGE for "CompetitorListRec"
> =Lists!$K$1:INDEX(CompetitorList,COUNTA(CompetitorList))
>
> Maybe change COUNTA(If(CompetitorList<>"",COUNTA, Don't count???
>
> I can't use the external table INDEX to restrict the named range list
> such as =Lists!$K
> $1:INDEX([DDLists.xls]CompetitorList,COUNTA([DDLists.xls]CompetitorList))
> because Data Validation DEMANDS an internal table. It is confusingly
> quite happy thinking that data in a cell that has a formula that
> results in getting data FROM an external table is an internal
> worksheet with internal values.
>
> Perhaps I can add code that checks the external table and when it
> grows to add the mirror If Then formula to a corresponding cell in col
> K in the internal worksheet. There must be a simpler way.
>
> Thanks for any help or advice,
> Dennis
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

On Aug 13, 12:01 pm, Debra Dalgleish <d...@contexturesXSPAM.com>
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