Excel 2003 Validation Lists Display but Don't Validate

HalArmstrong

New Member
Joined
Apr 12, 2011
Messages
3
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?
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Welcome to MrExcel.

Are you sure that you did this?

Check the checkbox -- Show error alert after invalid data is entered
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top