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

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" wrote in message
...
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

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.


--
Thiem
------------------------------------------------------------------------
Thiem's Profile: http://www.excelforum.com/member.php...o&userid=27474
View this thread: http://www.excelforum.com/showthread...hreadid=493734