Excel 2003 Validation Lists Display but Don't Validate

[HalArmstrong]

OBJECTIVE:

• Define list names and their valid values on one sheet in distinct columns on sheet UserDefinedTypes and validate user-entered data on other sheets.
• Defining a new data list:
  • Specify a List Name – On the UserDefinedTypes sheet, choose an unused column and enter a valid list name on row 1. For example, key-in ColorList in cell A1.
  • Specify the list data – If there are three valid data values, enter them in column A in rows 2, 3, and 4. I used Red, Blue, Green respectively.
  • Insert the list name
    • Excel menu Insert \ Name \ Define
    • In the Names in Workbook field, key in ColorList
    • In the Refers to field, key in:
      =OFFSET(UserDefinedTypes!$A$2,0,0,COUNTA(UserDefinedTypes!$A:$A),1)
    • Press OK
• Validating a column with a named data list:
  • Suppose we have an arbitrary sheet called MyRoom
  • Suppose we want column C to be restricted to the ColorList and have heading "My Wall Color"
  • Instructions:
    • On sheet MyRoom, select entire column C.
    • Specify data validation by:
      • Excel menu Data \ Validation – (Settings Tab)
      • Allow: List
      • Ignore Blank is checked
      • In-cell dropdown is checked
      • Source: =ColorList
    • Press the Error Alert tab:
      • Check the checkbox -- Show error alert after invalid data is entered
      • Select style -- Stop
      • Title -- (arbitrary) Value not in list
      • Error Message -- (arbitrary) Only blank and values in ColorList are permitted.
    • Press OK
    • Select Cell C1 and:
      • Excel menu Data \ Validation – (Settings Tab)
      • Allow: Any Value
      • Press OK
    • Key in the arbitrary heading for this column like "My Wall Color"

EXPECTATION:

• At this point, I would expect cell C1 to be exempt from validation and all other rows in column C to be bound to validation per ColorList or be blank.

PROBLEM:

• What I find is all rows in C (except the heading in C1) get the drop down of ColorList as expected but validation does not work. I can key-in Pink and there is no error dialog box.
• What is wrong?

 

[Andrew Poulsom]

Welcome to MrExcel.

Are you sure that you did this?

Check the checkbox -- Show error alert after invalid data is entered

 

[HalArmstrong]

Yes it is. See attached picture for additional details.

-- removed inline image ---

 

[Andrew Poulsom]

You have a blank at the bottom of your list. ColorList should be defined as:

=OFFSET(UserDefinedTypes!$A$2,0,0,COUNTA(UserDefinedTypes!$A:$A)-1,1)

 

[HalArmstrong]

Fantastic! Now it works like a charm.

You're top notch.