Custom data validation drop down lists

Hi all,

I'm trying to create an Excel program with drop down lists that can be easily customised by each user (and assuming no or very basic Excel knowledge on the part of each user). At the moment I have placed tick boxes via form control next to each of the possible options to include in the drop down lists. I've then linked each check box to a cell to get a TRUE/FALSE result and then used an IF statement to either display the option or a blank depending on whether or not the box is ticked (e.g. IF(A7,A1,"").

The problem is that when I go to make a drop down list in data validation Excel doesn't treat these cells as blanks so I get a drop down list with big spaces in it - does anyone know how I can resolve this issue?

Thanks in advance,

Laura

Originally Posted by Basil85

The problem is that when I go to make a drop down list in data validation Excel doesn't treat these cells as blanks so I get a drop down list with big spaces in it - does anyone know how I can resolve this issue?

If I'm understanding your issue correctly, then you can get around the spaces using a named range. If your data is in column A, create a helper column that automatically shuffles all of the spaces to the bottom. In (for instance) D1, use the following formula, array-entered with Ctrl + Shift + Enter instead of Enter:

=IFERROR(INDEX($A$1:$A$200,SMALL(IF($A$1:$A$200<>"",ROW($A$1:$A$200)),ROW(1:1))),"")

Then fill down as far as necessary. You can hide the helper column if it's unseemly. It'll automatically update as column A is altered, so you shouldn't need to actively use it. Once that's done, use the formula below as the "Refers To" formula when creating a named range to create a named range based on the helper column:

=OFFSET(Sheet1!$D$1,0,0,COUNTA(Sheet1!$D$1:$D$200),1)

Finally, use that named range as your data validation source. If your data stretches beyond row 200, then replace all of the 200s in the formulas with something more appropriate. See the attachment for a simplified look at how it works: