Data Validation - Ignoring Blank Cells in Drop Down List

[doublej41]

I am an Excel novice and would appreciate some help.

I have an excel file with 2 worksheets. I am trying to create a drop down list in column C on the front worksheet 'Codes', that references a list of names on the other worksheet 'Names'.

I am including a number of blank cells at the bottom of the list so that new names can be added to the list easily. When I create the list in this way I get a whole load of blanks included in my drop down list.

Is there a way I can create the drop down list whilst ignoring any blank cells in the range?

I have tried the following but get the error message "You cannot use references to other worksheets or workbooks for Data Validation criteria"

=OFFSET(Names!$B$2;COUNTBLANK(OFFSET(Names!$B$2; 0;0;COUNTA(Names!$B:$B);1));0;COUNTA(Names!$B:$B )-1;1)

I have included an image to indicate what I am trying to do. Any suggestions would be greatly appreciated.

 

[Ron Coderre]

I believe a Dynamic Range Name is what you need. It automatically expands/contracts to accommodate the data.

Data Validation cannot directly reference a range on another sheet.
However, DV can reference a Named Range...even if the destination is on another sheet.

See this site for instructions:
http://www.contextures.com/xlNames01.html#Dynamic

Pre-Excel 2007
• Insert.Name.Define
Excel 2007 and later
• Formula.Define_Name

Names in Workbook: LU_MyList
Refers to: =OFFSET(Names!$B$2;COUNTBLANK(OFFSET(Names!$B$2; 0;0;COUNTA(Names!$B:$B);1));0;COUNTA(Names!$B:$B )-1;1)

Then, when setting up your Data Validation...select the newly defined list name.

Does that help?

 

[doublej41]

That is spot on. Thank you very much for your help.

I was putting the formula into the DV source, whereas it needed to go under the Range Name.

Thanks again!