Data Validation - Excel 2000

[Pinda]

I have validated some cells with a list and a drop-down
menu of possible options by using a cell reference defined
by a name.

Within this cell reference, there are blank cells, if I
need to insert additional data, thus the list will
automatically be updated.

However, in the data validation box, when I tick 'ignore
blank cells', I still have blank cells within my drop down
menu.

Is this a bug within excel 2000? Could somebody please
advise?

Many Thanks,

Pinda

 

[Andy Brown]

"Pinda",

However, in the data validation box, when I tick 'ignore
blank cells', I still have blank cells within my drop down
menu.

That's not the purpose of 'ignore blank cells'.

You can work round this using Chip's method at
http://www.cpearson.com/excel/noblanks.htm
, then adding another name for MyList which refers to
=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B)-(COUNTBLANK(NoBlanksRange)),1)

, where eg: BlanksRange =Sheet1!$A$1:$A$5, and NoBlanksRange
=Sheet1!$B$1:$B$5.

If you can't figure it, mail me direct and I'll send a tiny demo file.

Rgds,
Andy