Category: Excel Data Validation

Data validation is one of the best features in Excel. You can use it to create drop down lists, or limit what users can enter in a cell.

Unfortunately, data validation isn’t perfect, or foolproof. Users can get around the limits, by pasting data into the cell, or by using the Clear All command in a data validation cell.

Data Validation Input Errors

Someone sent me a question this week, asking how to trap input errors, despite these data validation failings:

• Hello! I was wondering, to you have any idea how to error trap a date input? I will enter dates on a specific column (complete dates such as 11/05/2010) and error trap if the input has the year of 2011 and not 2010. I know data validation does that, but only if the cell is manually inputted. If the input in the cell is pasted, it does not do that anymore.

Add the Data Validation

In this example, an order form has two cells for dates – an Order Date, and a Delivery Date. To ensure that a date for the current year is entered, you can use formulas in the data validation, to set a minimum and a maximum date.

Start Date formula: =DATE(YEAR(TODAY()),1,1)

End Date formula: =DATE(YEAR(TODAY()),12,31)

Check the Date

If you’re concerned that users might paste values into the cell, or clear the data validation, you can add a formula check, to ensure that a valid date was entered.

In the order form, a date check formula is entered in column G, which can be hidden.

The formula in cell G9 is:

=OR(C9=””,YEAR(C9)=YEAR(TODAY()))

The formula is copied down to cell G10, and the result is FALSE, because the date in C10 is not in the current year.

Block the Invoice Total

In the Invoice total cell, the formula result is “Invalid Date”, if either of the date check cells contains FALSE. If both dates are in the current year, the total sum is shown.

Here is the formula from the Invoice Total cell:

=IF(COUNTIF(G9:G10,FALSE),”Invalid Date”,SUM(E13:E17))

Other Solutions

Instead of a formula check, there are other ways to ensure that users enter valid data.

For example, you could use Excel VBA to check specific cells before printing, and cancel the printing if the entries aren’t valid.

Have you used other methods to ensure that users don’t ignore your data validation cells?
____________

With Excel data validation, you can create drop down lists on a worksheet. However, the font size is very small, and can’t be adjusted, and you can only see 8 items at at time.

ComboBox on Worksheet

With Excel VBA programming, you can add a ComboBox to the worksheet, to show the data validation list.

In the ComboBox, you can control the font size and the number of visible items in the list.

Problems in Excel 2010

Although this technique works nicely in Excel 2007, and earlier versions, you might have a problem with the ComboBox size in Excel 2010.

In the screen shot below, the ComboBox is about 1/4″ wide, instead of filling the entire cell.

In other workbooks, the ComboBox is so narrow that you can’t see it at all. That’s not too helpful a feature!

Fix the Problem in Excel 2010

Fortunately, the problem is easy to fix in Excel 2010, if you follow these steps.

On the Developer tab, click the Design Mode command.

To select the ComboBox, type its name in the Name Box, and press Enter

On the Ribbon, under Drawing Tools, click Format, and click the Dialog Launcher for the Size group.

Format Shape Dialog Box

In the Format Shape dialog box, in the Size category, remove the check mark for Lock Aspect Ratio, and click OK

That should fix the ComboBox sizing problem!

Watch the Combo Box Sizing Video

To see the steps for changing the Size setting in Excel 2010, you can watch this short Excel video tutorial.

___________

On Monday, AlexJ showed us how to create a short or long drop down list in Excel. With his technique, users can see just the top customers, or all customers.

That technique didn’t require macros — it was driven by a formula in the data validation source.

Long or Short List Macro

Today, Alex shares an automated version of the short or long data validation list technique. Starting in the next section, you can read his description of how this version works.

You can download the zipped Dynamic Data Validation Sample File from the Contextures website. The file contains macros, so enable them to use the dynamic drop down list.

Dynamic Data Validation Lists

For an Excel utility running at our office, users are required to enter a project number using a drop down list. There are thousands of these records in the data set, selecting from hundreds of project numbers. This means that the drop-down list is long, and therefore not very useful.

To address this, we determined that the user would usually select from a short list of active projects, but would also need to select from a long list of all projects or old projects.

Use a Single Drop Down

There are a number of techniques using dependent data validation in Excel, but these usually require two selection boxes, we wanted to do this with only a single drop down selection.

The technique presented allows the user to select from a default list of entries, or select a different list.

How It Works

The two lists are named — rng.DD1 for the new projects, and rng.DD2 for the full project list. The first cell in each list is a formula, that refers to the other list.

=”>> GOTO ” & $J$3

The cell with the drop down list is named rng.DD_Select.

Calculate Which List Selected

The result cell, $E$5, calculates which list has been selected:

=”rng.DD”&IF(rng.DD_Select=$J$3,2,1)

If the selected item matches the heading in cell J3, the result is rng.DD2, otherwise, the result is rng.DD1.

The Data Validation

The data entry cell has data validation configured for a list, and the following formula that refers to the result cell:

=INDIRECT($E$5)

If the result in cell $E$5 is rng.DD1, the new project list is shown.

The Programming

The data validation doesn’t require programming, but there is a small VBA routine triggered by the Change Event in cell B5. It tidies up the data entry cell, after a selection is made.

This routine will:

1. Clear any entries from the list where the user has selected “——–”, or a list header like “—– xxxx ——–”
2. Convert a selection like “>>>> GOTO NEW PROJECT LIST” to “NEW PROJECT LIST”

Excel Event Code

Here is the event code from the data entry sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim str As String
Dim strNew As String
Const strMatch As String = ">> GOTO "
If Target.Address = Me.Range("rng.DD_Select").Address Then
  str = Target.Value
  If str Like "-*" Then
    Target.ClearContents
  Else
    If str Like strMatch & "*" Then
      strNew = Right(str, Len(str) - Len(strMatch))
      Target.Value = strNew
    End If
  End If
End If
End Sub

________________

Can the sales staff and accounting staff ever work in peace? One group wants to see product descriptions, when entering orders. The other group thinks the descriptions clutter up the worksheet — they just want the product codes.

Try this data validation trick, and you might be nominated for next year’s Nobel Peace Prize. (Results not guaranteed.)

See the workbook details below, and there’s a video with step-by-step instructions at the end of the page.

Create Drop Down Lists

To make it easier for users to enter data in an Excel workbook, you can create drop down lists in the cells, by using Excel data validation.

Product Lookup Table

In this example the product list is in an Excel Table, and the ProductShow column is a named range — ProdList.

The ProdList range is used as the source for the drop down lists on the order entry sheet.

Add Excel VBA Magic

After the product is selected from the drop down list, the full description is automatically replaced by the product code. How does it happen?

It’s the magic of Excel VBA — event code that runs when the worksheet is changed.

Drop Down List VBA Code

The Excel VBA code uses the Match worksheet function to find the row number in the lookup list. It replaces the selected product description with the matching Product Code from that row in the lookup list.

Peace at last! Your co-workers will be happy that they don’t have to memorize the product codes, and the accounting department will be grateful that they get the data in the format they need.

Download the Sample File

To see the Excel VBA code that changes the product name to a product code, go to the Contextures website, and download the sample file: DV004: Data Validation Change.

The example used here is the Excel 2007 version, and there is also an Excel 2003 version of the sample file.

Watch the Data Validation Video

You can watch this video to see the steps for creating an Excel Table, naming a column in that table, then using that name when creating the data validation drop down list.

___________