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

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

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