Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation dropdown shows blank choices
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(Competitor List)) 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Eliminating choices on data validation | Excel Programming | |||
Autofilter dropdown box doesn't show all the choices in the colum | Excel Discussion (Misc queries) | |||
Choices from Data Validation List | Excel Discussion (Misc queries) | |||
Excel filter dropdown shows all data | Excel Worksheet Functions | |||
Autofil color to a row from dropdown list choices | Excel Worksheet Functions |