HalArmstrong
New Member
- Joined
- Apr 12, 2011
- Messages
- 3
OBJECTIVE:
PROBLEM:
- 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"
- 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: