| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove empty cells from named list / validation list
Hi, I have defined a named list (A1:A500 named CompanyNames). The list currently only has 3 items but gradually companies will be added to this list. In a second sheet I have specified the list CompanyNames under validation for all cells in column B. When I click a cell with this validation defined I only want to see the values of the cells that have data in it. Currently I see a list with 3 company names and 497 blank lines. Is it possible to only see the 3 companany names in the list and not the blank cells? Thanks in advance for your help. Sp00k -- Sp00k ------------------------------------------------------------------------ Sp00k's Profile: http://www.excelforum.com/member.php...o&userid=33676 View this thread: http://www.excelforum.com/showthread...hreadid=536465 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove empty cells from named list / validation list
Isn't there an "ignore blank cells" check box when you go to select the cells
for you list? I think it is on the right side Neil "Sp00k" wrote: Hi, I have defined a named list (A1:A500 named CompanyNames). The list currently only has 3 items but gradually companies will be added to this list. In a second sheet I have specified the list CompanyNames under validation for all cells in column B. When I click a cell with this validation defined I only want to see the values of the cells that have data in it. Currently I see a list with 3 company names and 497 blank lines. Is it possible to only see the 3 companany names in the list and not the blank cells? Thanks in advance for your help. Sp00k -- Sp00k ------------------------------------------------------------------------ Sp00k's Profile: http://www.excelforum.com/member.php...o&userid=33676 View this thread: http://www.excelforum.com/showthread...hreadid=536465 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove empty cells from named list / validation list
Define CompanyNames with a RefersTo value of
=OFFSET($A$1,,,COUNTA($A:$A),1) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Sp00k" wrote in message ... Hi, I have defined a named list (A1:A500 named CompanyNames). The list currently only has 3 items but gradually companies will be added to this list. In a second sheet I have specified the list CompanyNames under validation for all cells in column B. When I click a cell with this validation defined I only want to see the values of the cells that have data in it. Currently I see a list with 3 company names and 497 blank lines. Is it possible to only see the 3 companany names in the list and not the blank cells? Thanks in advance for your help. Sp00k -- Sp00k ------------------------------------------------------------------------ Sp00k's Profile: http://www.excelforum.com/member.php...o&userid=33676 View this thread: http://www.excelforum.com/showthread...hreadid=536465 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove empty cells from named list / validation list
There is, but it doesn't appear to do a lot. -- Sp00k ------------------------------------------------------------------------ Sp00k's Profile: http://www.excelforum.com/member.php...o&userid=33676 View this thread: http://www.excelforum.com/showthread...hreadid=536465 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove empty cells from named list / validation list
Hi Bob, It took me a while to get it right (newbie problems:) ) but I got it working in the end with your advice. Thanks a lot for your help. Sp00k -- Sp00k ------------------------------------------------------------------------ Sp00k's Profile: http://www.excelforum.com/member.php...o&userid=33676 View this thread: http://www.excelforum.com/showthread...hreadid=536465 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dependent List (via Data Validation) Error | Excel Worksheet Functions | |||
| Excel - Autom. Filter "Empty / Non Empty cells" should come first | Excel Discussion (Misc queries) | |||
| named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
| Counting empty cells within a range of cells | New Users to Excel | |||
| Counting empty cells within a range of cells | New Users to Excel | |||
Linear Mode
