Data Validation range

[Nigel]

Hi,
in my book i have a cell with data validation set up to collect data from a
range B5:B1005. every month, this range is empty and gets filled up
throughout the month. my problem is as i have set the validation range to
look at the range B5:B1005, i get a massive dropdown box which ( when i have
only 20 items ), is alot of space. can the validation range be sort of active
so if there is only 20 items in the range, it only lists the 20 items without
the other 800 spaces from the empty cell?
i thought maybe a named range but i cannot get it to do what i need.

any ideas greatly appreciated.

regs,

Nigel

[Bob Phillips]

Try it with a formula of

=OFFSET($B$5,,,COUNTA($B$5:$B$105),1)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Nigel" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> in my book i have a cell with data validation set up to collect data from
a
> range B5:B1005. every month, this range is empty and gets filled up
> throughout the month. my problem is as i have set the validation range to
> look at the range B5:B1005, i get a massive dropdown box which ( when i
have
> only 20 items ), is alot of space. can the validation range be sort of
active
> so if there is only 20 items in the range, it only lists the 20 items
without
> the other 800 spaces from the empty cell?
> i thought maybe a named range but i cannot get it to do what i need.
>
> any ideas greatly appreciated.
>
> regs,
>
> Nigel
>
>

[Thiem]

As long as there are empty cells between two on more entries in the range, you will sit with a massive ddl. try to remove emtly cells with a macro, or just make sure you enter values without any empty cells in the range.