Validation list - not null

**m.cain** · 06-27-2012, 01:13 PM

Hello,

I have set a cell validation to list referencing a column (i.e., the user has the ability to add new values to that list) but I do not want the user to enter null values (i.e., leave blank) in the cell.

How do I get around the dynamic list?

Thanks.

**ChemistB** · 06-27-2012, 01:21 PM

Is the problem that in your list you have blank values or just that Excel lets users enter blanks in your DV cell?

If the second, in the data validation window, uncheck "Ignore blanks"
If the initial, use a dynamic range so you don't need to include blanks at the end of it.
Does that help?

**dilipandey** · 06-27-2012, 01:28 PM

Hi m.cain,

You can trace null values by using formula CountBlank
but I guess range should be known to apply the logic.. could you share the sample workbook... thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**m.cain** · 06-27-2012, 01:59 PM

Hello,

Lets assume 2 worksheets.

In the first is a column/field containing a basic list. The user may add to this list or remove from this list. Blanks inevitably occur for all cells that do not have data entered by the user.

The 2nd sheet contains a cell with a validation list set. The user can select from a drop down list in the cell any of the values that appear in the previous sheet's column.

I do not want the user to leave that cell blank though.

Originally we'd thought of simply using some basic VBA code to check when all was done whether that cell was left blank (i.e., "") and if so then it returned a message asking the user to input a value. This didn't work though as it didn't recognize when the user did select a value from the list (i.e., it still assumed it was blank) and would only recognize when the user physically typed in a value. Basically not good.

Using the Validation would be so much easier. However since the list is based on an open ended column it ignores the "ignore blank" function entirely (i.e, turning that option off doesn't affect nulls).

If there was say an IF statement that I could use that said something like =IF(A1<>"", ListField, "") then maybe that option might work, but unfortunately "" is not a list itself so it doesn't like that.

**ChemistB** · 06-27-2012, 02:16 PM

1. Not sure what other calculations you have on your sheet but you can incorporate a check in the formula on that cell before doing the calculation.
For example, if your data val cell is A1, and you have calculation column of the form =VLOOKUP($A$1, Range,2,FALSE), you can change that to
=IF($A$1<>0, VLOOKUP($A$1,Range,2,FALSE),"A value must be entered into A1") Do this on all formulas on the sheet.

2. You can use VBA to check on that DV cell whenever a manual entry is performed or prior to saving or printing. Depending on how complex your formulas are, this might be the best option.

**m.cain** · 06-27-2012, 03:06 PM

Here is a very basic example of the scenario (see attachment). I don't want the user to be able to leave cell B2 blank in the DataSheet. The user can add as many new elements to column A in ListSheet as they want and it must be reflected automatically in the list in the DataSheet drop down.

As you can see there are no complicated formulas or vlookups involved here.

**icestationzbra** · 06-27-2012, 03:53 PM

EDIT: deleted attachment

apologies to OP: completely misunderstood requirements...

**m.cain** · 06-27-2012, 04:34 PM

Thank you for your thoughts, however this still allows the cell to be blank. The whole purpose is to not allow blanks.

I would also need to copy said helper column formulas down X number of cells (if copied to full column length, there will be a significant lag time I would expect, not to mention a jump in file size, no?) and then hide that column from the user. Some maintenance would also be required if the user started adding or deleting records in that sheet I would expect.

**icestationzbra** · 06-27-2012, 04:41 PM

here is a rudimentary macro to let users know that they need to enter a value in cell D2, if they have navigated to it and then navigated away without selecting a value.

you could get better solutions if you posted this question in the Programming section.

**m.cain** · 06-27-2012, 04:56 PM

Indeed. As I mentioned earlier I did previously use VBA to prompt the user to enter a value, except that the VBA would not recognize that a value had been entered if selected from the drop down list.