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

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

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

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

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