S
ssGuru
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
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