Remove empty cells from named list / validation list

[Sp00k]

Hi,

I have defined a named list (A1:A500 named CompanyNames). The lis
currently only has 3 items but gradually companies will be added t
this list. In a second sheet I have specified the list CompanyName
under validation for all cells in column B. When I click a cell wit
this validation defined I only want to see the values of the cells tha
have data in it. Currently I see a list with 3 company names and 49
blank lines. Is it possible to only see the 3 companany names in th
list and not the blank cells?

Thanks in advance for your help.

Sp00

 

[Guest]

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

 

[Bob Phillips]

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]

There is, but it doesn't appear to do a lot.

 

[Sp00k]

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