Using Formulas for Data Validation
 

Hi,
I have an Excel 2003 form that has two interrelated combo boxes that pull
from two lists on a separate tab. C4 is for Class of product and C5 is for
Family. The Family can only belong to one Class.

Data Validation for C4 is:
=IF(C5="",ClassList,INDEX(ClassColumn,MATCH(C5,Fam ilyColumn,0)))

Data Validation for C5 is:
=OFFSET(ClassStart,MATCH(C4,ClassColumn,0)-1,1,COUNTIF(ClassColumn,C4),1)

This works to restrict users to choosing a Class then a Family and restricts
the Family list by the chosen Class.

The problem is, I set Data Validation to stop the user if they type
something in either cell that is not on the list, but it doesn't work.
Anything typed is accepted. Without the formulas, users can only choose from
the lists, but they would see all the Family types instead of only those
related to the Class, and could choose one that is not appropriate.

I need for the users to get an error message if they type something in
either cell that is not on the list. Any suggestions?

Thanks,
Bernie

Using Formulas for Data Validation
 

I may have found my solution. I deselected Ignore Blank on the Data
Validation form, and it seems to be working now. I couldn't type anything in
C4 without getting an error message. If this is foolproof, I'm happy, if not,
does anyone know of a foolproof method?

And remember: "Anytime I make something foolproof, they create a better fool."

"berniean" wrote:

Hi,
I have an Excel 2003 form that has two interrelated combo boxes that pull
from two lists on a separate tab. C4 is for Class of product and C5 is for
Family. The Family can only belong to one Class.

Data Validation for C4 is:
=IF(C5="",ClassList,INDEX(ClassColumn,MATCH(C5,Fam ilyColumn,0)))

Data Validation for C5 is:
=OFFSET(ClassStart,MATCH(C4,ClassColumn,0)-1,1,COUNTIF(ClassColumn,C4),1)

This works to restrict users to choosing a Class then a Family and restricts
the Family list by the chosen Class.

The problem is, I set Data Validation to stop the user if they type
something in either cell that is not on the list, but it doesn't work.
Anything typed is accepted. Without the formulas, users can only choose from
the lists, but they would see all the Family types instead of only those
related to the Class, and could choose one that is not appropriate.

I need for the users to get an error message if they type something in
either cell that is not on the list. Any suggestions?

Thanks,
Bernie