Data Validation unwanted spaces

Question

Dearest Wizards,

I have a Data Validation list (A1:A100) that is ever changing in both, the elements in the cells and the number of cells “needed”.  I ‘probably’ don’t need more than 70 but I overshot (100) just to make sure it could accommodate the additional numbers.  In my “DropDown” cell (B1) I can see all of the selections in the validation list but I also see all of the empty spaces.  I can solve the extra space problem with a VBA script but is there a Non-VBA (formula) way to do this?

TIA,

Sam

Answer 1

You can use a dynamic named range that automatically adjusts itself as you add or remove entries in A1:A100.

• On the Formulas tab of the ribbon, click Define Name.
• In the Name box, enter (for example)    MyList
• In the Refers to box, enter the formula   =OFFSET($A$1,0,0,COUNTA($A$1:$A$100),1)
• Click OK.

Next, we'll make the data validation rule use the named range.

• Select B1 (the cell with the dropdown).
• On the Data tab of the ribbon, click Data Validation.
• In the Source box, enter the formula    =MyList    using the name that you specified above.
• Click OK.

Answer 2

Hi,

Assume your data is in range A1:A70, select it and convert it to a Table.  Now provide A1:A70 as the source in Data > Validation.  When you add data from cell A71 downwards, the validation range will automatically expand.

Hope this helps.